VBA Vlook Up formula using R1C1

[miltonjeff]

I have started just recently into VBA. I am working on a vlookup formula. Thought I could simplify it using an R1C1 formula but it won't work for me.

I am looking up one one sheet and referencing another sheet. I have about 50 rows of data list.

Here is the VBA code that is working for me:

Sub vsrch()


Dim ws1 As Worksheet, ws2 As Worksheet
Dim srchres As Variant

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Staff Listing")

srchres = Application.WorksheetFunction.VLookup(ws1.Range("a2"), ws2.Range("A2:C100"), 2, False)
ws1.Range("d2").Value = srchres
srchres = Application.WorksheetFunction.VLookup(ws1.Range("a2"), ws2.Range("A2:C100"), 3, False)
ws1.Range("e2").Value = srchres
srchres = Application.WorksheetFunction.VLookup(ws1.Range("a3"), ws2.Range("A2:C100"), 2, False)
ws1.Range("d3").Value = srchres
srchres = Application.WorksheetFunction.VLookup(ws1.Range("a3"), ws2.Range("A2:C100"), 3, False)
ws1.Range("e3").Value = srchres

End Sub


Here is the VBA with the R1C1 that is not working for me (Once i figure out the issue then I would add the next column of data.

Sub VlookupVBA()
Dim lLastRow As Long

With Sheets("Sheet1")
lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("d1:d" & lLastRow)
.FormulaR1C1 = "=VLOOKUP(RC[-1],'Staff Listing'!a2:c100,2,FALSE)"
.Value = .Value
End With
End With

End Sub

Any help would be greatly appreciated.

 

[crazydragon84]

lLastRow = .Range("A" & .rows.count).end(xlup).row

.FormulaR1C1 = "=VLOOKUP(RC[-1],'Staff Listing'!a2:c100,2,FALSE)" <- A2 and C100 needs to convert to R1C1, so R2C1 and R100C3

 

[miltonjeff]

lLastRow = .Range("A" & .rows.count).end(xlup).row

.FormulaR1C1 = "=VLOOKUP(RC[-1],'Staff Listing'!a2:c100,2,FALSE)" <- A2 and C100 needs to convert to R1C1, so R2C1 and R100C3

Thanks, for the fix, just had to change to

=VLOOKUP(RC1,

and it worked like a charm!

Thanks!

 

[crazydragon84]

great! glad it was helpful