VBA Vlookup Range not working

[ArrFin30H]

I'm trying to write a macro in vba that will select a certain group of cells and set them as a range then perform a vlookup against that range all on the same sheet. My macro starts by jumping around the sheet to select the first range as rng1(usually the first 100 rows up to column R on the sheet). That part seems to work. But when I try to run a vlookup against rng1 the vlookup treats rng1 as cell "A1" only and not the first 100 rows up to column R. Here is an edited version of the code. Any help would be much appreciated.

Dim rng1 As Range
Dim r As Long


ActiveSheet.Range("I65536").Select
Selection.End(xlUp).Select
r = Selection.Row
Range("A1:R" & r).Select

Set rng1 = Selection

Range("e" & r).Offset(1, 0).Select

ActiveCell.Formula = "=vlookup(RC[-3],rng1,2,0)"

 

[xenou]

I suspect the problem is:
ActiveCell.Formula = "=vlookup(RC[-3],rng1,2,0)"

Putting rng1 inside the quotes will give you a bad looking formula. Instead try:
ActiveCell.Formula = "=vlookup(RC[-3]," & rng1.Address & ",2,0)"

I believe is is a good habit to build your string first, then use it in the formula. The code below will also clean up your code to avoid the "jumping around" - i.e., don't use selection unless necessary. Of course, I added a number of annoying message boxes You can remove those - its more to demonstrate a debugging method for future reference.

Example:
Range("A1").Select
Selection.Interior.ColorIndex = 6

Can be edited to:
Range("A1").Interior.ColorIndex = 6

---------------------------------

Sub Test()

Dim rng1 As Range
Dim LastRow As Long

LastRow = Range("I65536").End(xlUp).Row
MsgBox "LastRow is: " & LastRow

Set rng1 = Range("A1:R" & LastRow)
MsgBox "rng1 is: " & rng1.Address

strFormula = "=vlookup(RC[-3]," & rng1.Address & ",2,0)"
MsgBox "Formula is: " & strFormula

Range("e" & LastRow).Offset(1, 0).FormulaR1C1 = "strFormula"

End Sub

 

[ArrFin30H]

Thanks for replying.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I entered your code and the message box confirmed that the range and the strformula were correct. Instead of the vlookup formula being returned in the active cell I got "strformula". I tried many different ways but I'm still unable to get it to show the actual vlookup formula in the cell. Do you have any suggestions that I could try?

 

[xenou]

Sorry, I am an idiot.

NOT:
Range("e" & LastRow).Offset(1, 0).FormulaR1C1 = "strFormula"

INSTEAD:
Range("e" & LastRow).Offset(1, 0).FormulaR1C1 = strFormula

AB

 

[ArrFin30H]

When I try that I get

run-time error '1004':
Application-defined or object-defined error.

I tried declaring strformula as string but that did not work either.

 

[xenou]

Sorry. Another try:

Sub TestMe()

Dim rng1 As Range
Dim LastRow As Long
Dim strFormula As String

LastRow = Range("I65536").End(xlUp).Row
Debug.Print "LastRow is: " & LastRow

Set rng1 = Range("A1:R" & LastRow)
Debug.Print "rng1 is: " & rng1.Address

strFormula = "=vlookup(RC[-3]," & rng1.Address(True, True, xlR1C1) & ",2,0)"
Debug.Print "Formula is: " & strFormula

Range("e" & LastRow).Offset(1, 0).FormulaR1C1 = strFormula

End Sub

We are mixing R1C1 and A1 style references in strFormula

 

[ArrFin30H]

It worked.
Thanks a lot Alexander!