Trying to do vloopup in VBA getting a #value error

[vba317]

I am trying to do a vlookup in VBA and I am getting an #VALUE error. I am trying to match values in column A of a sheet called data with the values from column B on a sheet called Default Pt List. On a match I want the value to go in Column H of the sheet called data. Any help is appreciated.

Tom

On Error Resume Next
  For i = 2 To lLastRow
       Worksheets("Data").Cells(i, "H") = Application.WorksheetFunction.VLookup(Worksheets("Default Pt List").Range("A2:A" & lLastRow), Worksheets("Data").Range("B2:B" & lLastRow), "B")
  Next i

 

[mole999]

The data you are working with is it numbers or text, do they have spaces around them

also a named range can be easier to workwith, so code for Default Pt List to be extensible / rebuilt and then that is easier than

VLOOKUP(item to find, the range where the data is, the column the data to return lives in, exact / or inexact match)

maybe build the vlookup, then record it in a macro and see what it looks like

Range("A1").FormulaR1C1 = "=VLOOKUP(R[1]C[1],Wednesday,12,FALSE)"

 

[vba317]

I have never used R1C1 notation before how does it work? I am not getting an error but There are no results in Column F either.

                    For i = 2 To lLastRow
                        Worksheets("Data").Range("F" & i).FormulaR1C1 = "=VLOOKUP(R[1]C[1],'PatientList!A:C',3,FALSE)"
                    Next i

 

[mole999]

R1C1 is what my excel generates when I have populated a cell with an appropriate code, then run macro recorder, delete and replace the end character and I have a piece of VBA.

Does the formula copy down with the code and does it look right ?

 

[vba317]

I am sorry but I couldn't figure out how to do that. I did try another way and it works so I am going with it. Thanks for your help.

                    With Range("F1:F" & lLastRow)
                        .Formula = "=VLOOKUP(B1,PatientList!A:C,3,False)"
                        .Value = .Value
                    End With