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").ra nge("E2:e100")
set wkbkBRng =
workbooks("workbookB.xls").worksheets("sheet2").ra nge("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
with wrkbkbrng.parent 'sheet2 in workbookB.xls
.cells(.rows.count,"A").end(xlup).offset(1,0).valu e = mycell.value
end with
else
mycell.offset(0,1).copy _
destination:=wkbkbrng(res).offset(0,1)
end if
next mycell

----
I don't know if you want to change this line:
set wkbkBRng = workbooks("workbookB.xls").worksheets("sheet2").ra nge("a2:a100")
to
set wkbkBRng = workbooks("workbookB.xls").worksheets("sheet2").ra nge("a:a")

to include that new value in the next search.



Roger wrote:

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").ra nge("E2:e100")
set wkbkBRng =
workbooks("workbookB.xls").worksheets("sheet2").ra nge("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

--

Dave Peterson