I am trying to get this to work:
Sub vlookup()
For Each Cel In Range("H2:H100")
If Cel.Value <> "" Then Cel.Offset(0, 2).Value = "=VLOOKUP(CONCATENATE(RC[-9],RC[-8]),Sheet1!$A$2:$K$38,11,FALSE)"
Next
End Sub
but get a Run-time error '1004': Application-defined or object-defined error
I do not understand as the relative concatenate works, and the vlookup with static references work, but when combining them I get this error...
Concatenate with relative references that works:
Sub vlookup()
For Each Cel In Range("H2:H100")
If Cel.Value <> "" Then Cel.Offset(0, 2).Value = "=CONCATENATE(RC[-9],RC[-8])"
Next
End Sub
vlookup with static references that works:
Sub vlookup()
For Each Cel In Range("H2:H100")
If Cel.Value <> "" Then Cel.Offset(0, 2).Value = "=VLOOKUP(CONCATENATE(A2,B2),Sheet1!$A$2:$K$38,11,FALSE)"
Next
End Sub
Bookmarks