R
Roger
Hi Dave,
Sorry to be a pain, but I have one last question.
In the macro you had helped me with, I tried to add an additional "else" in
the event no match is found. Basically, this program is set-up to look for
matches and write the data to the corresponding cells which works great.
However, I've been trying to add additional code( with no luck) to have it
step down to first empty cell in Sheet2 and write the unmatched value from
Sheet1 in the event no Match is found.
I tried using something along the lines -.end (x1down).row - plus what I've
noted in the Macro, but I've had no luck. The best I could do was to get it
to add multiple empty rows instead of adding just the one value for the
unmatched cell.
I appreciate your review and this should finally put this particular routine
to rest.
Thanks - Roger
__________________________________________________________________
Dim WkbkARng as range
dim WkbkBRng as range
dim myCell as range
dim res as variant 'could be a number or an error
set wkbkARng =
workbooks("workbookA.xls").worksheets("sheet1").range("E2:e100")
set wkbkBRng =
workbooks("workbookB.xls").worksheets("sheet2").range("a2:a100") << looking
for match in same row and column D to be < ""
for each mycell in wkbkarng.cells
res = application.match(mycell.value,wkbkbrng,0)
if iserror(res) then
If Not wkbkbrng(res).Offset(0, 4).Value <> "" Then
.............copy and write cells to offset of unmatchched value
to next empty cell......
<<<<tried adding irow here in event of no match - failed>>>
else
mycell.offset(0,1).copy _
destination:=wkbkbrng(res).offset(0,1)
end if
next mycell
Sorry to be a pain, but I have one last question.
In the macro you had helped me with, I tried to add an additional "else" in
the event no match is found. Basically, this program is set-up to look for
matches and write the data to the corresponding cells which works great.
However, I've been trying to add additional code( with no luck) to have it
step down to first empty cell in Sheet2 and write the unmatched value from
Sheet1 in the event no Match is found.
I tried using something along the lines -.end (x1down).row - plus what I've
noted in the Macro, but I've had no luck. The best I could do was to get it
to add multiple empty rows instead of adding just the one value for the
unmatched cell.
I appreciate your review and this should finally put this particular routine
to rest.
Thanks - Roger
__________________________________________________________________
Dim WkbkARng as range
dim WkbkBRng as range
dim myCell as range
dim res as variant 'could be a number or an error
set wkbkARng =
workbooks("workbookA.xls").worksheets("sheet1").range("E2:e100")
set wkbkBRng =
workbooks("workbookB.xls").worksheets("sheet2").range("a2:a100") << looking
for match in same row and column D to be < ""
for each mycell in wkbkarng.cells
res = application.match(mycell.value,wkbkbrng,0)
if iserror(res) then
If Not wkbkbrng(res).Offset(0, 4).Value <> "" Then
.............copy and write cells to offset of unmatchched value
to next empty cell......
<<<<tried adding irow here in event of no match - failed>>>
else
mycell.offset(0,1).copy _
destination:=wkbkbrng(res).offset(0,1)
end if
next mycell