Follow-up Question for Dave Peterson

[Roger]

Good Morning Dave,

I'm wondering if it's possible to add an additional condition to the Match
Row for the Sheet1 look-up?

After the Sheet1 Match is found in Sheet2 Column A, I'm also looking to add
a condition so that Sheet2 Column D is < "" before returning the Match value
to the noted sheet location.

Is that a possibility?

Thank you for your review once again - 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

else
mycell.offset(0,1).copy _
destination:=wkbkbrng(res).offset(0,1)

end if
next mycell

 

[Dave Peterson]

I'm not sure what
< ""
means

But you could add the condition here:

for each mycell in wkbkarng.cells
res = application.match(mycell.value,wkbkbrng,0)
if iserror(res) then
'do nothing
else
'change this to what you mean
if wkbkbrng(res).offset(0,1).value < 9999 then
'do the work
mycell.offset(0,1).copy _
destination:=wkbkbrng(res).offset(0,1)
else
'skip it
end if
end if
next mycell

 

[Roger]

Thank you so much Dave - exactly what I needed and works great.

<"" meant greater than nothing, but I should have actually written it out.

Thanks again and you're the best - Roger

 

[Dave Peterson]

I'd use either:
if wkbkbrng(res).offset(0,1).value <> "" then
or even
if trim(wkbkbrng(res).offset(0,1).value) <> "" then