VBA Excel LookUP Function

[nquadr]

Excel VBA Lookup Help

Hi I am having trouble with my VBA Lookup function. I have created 2 workbooks which I have instantiated to wbOld and wbNew. I am taking a column from wbOld and comparing with a range in wbNew. I want it to return the second column, with an exact match. Unfortunately, it is not working. I believe it to be a problem with my syntax. Can someone help. I am new to VBA.

Sub Comparison()
    Dim xOld As Integer
    xOld = 2






    wbOld.Application.ActiveSheet.Cells(xOld, 21) = _
    wbOld.Application.WorksheetFunction.VLookup(Cells(xOld, 19), _
    wbNew.Application.ActiveSheet.Range("S:T"), 2, 0)





End Sub

Eventually, I will get it to loop. If I can get it to work. Your help would be greatly appreciated. Thanks.

[DaliborCZ]

My opinion is, that cells(xOld,19) refers to the activeWorkbook and ActiveSheet. If you have not activated wbOld, it shoud refer elsewere. Also the refer to the activesheet would not function this way. Better is

 wbOld.activesheet.cells(xOld,21)

but you are under risk that you work with other sheet that you believe to do.
Second - as you are using parameter "0" of exact match, the Vlookup function is not very good solution. In this case it is better to use Find method and test the result for Nothing:

dim rng as Range
set rng= wbNew.Worksheets("someNew").range("S:T").Find(wbOld.Worksheets("someOld").cells(xOld,19).value)
if rng is Nothing then
'do some measures for not found
else
    wbOld.Worksheets("someOld").cells(xOld,21).value=rng.value
endif

[nquadr]

wbOld.Worksheets("someOld").cells(xOld,21).value=rng.value

For the above code is there a way just to return the value of what I have in the T column of my range? Right now the code is just giving me the value of the "S" column. But this code helped tremendously. Thank you I have been trying to get this to work for days.

[nquadr]

wbOld.Worksheets(1).Cells(xOld, 21).Value = rng.Offset(0, 1).Value

I did this it seems to work. Thanks so much for all your help.

[DaliborCZ]

Sometimes .Worksheets(1) could be fine, but whenever you have more sheets candidates, it is better to refer by name: Worksheets("Sheet7") or Worksheets("Mydata")

[nquadr]

Thanks again.