Vlookup .. showing syntax error plz help

[Mallesh23]

Hi Team,

I am trying different option of using vlookup. only single formula working. when I use defined my criteria range as MyData
other are showing errors. please assist where its going wrong.

My Criteria range is in sheet3 (c6:f11) , my lookupvalue is in sheet1(a2), I am running vlookup in sheet1("b2").

Please assist.
below are my code.

Option Explicit

Sub Pranay()

Dim rs As Range
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

Set rs = ThisWorkbook.Sheets(3).Range("C6:F11").Value


'Sheets(1).Range("b2:b" & lr).Formula = "=vlookup(a2,MyData,2,false)"

Sheets(1).Range("b2:b" & lr).Formula = "=vlookup(a2,rs,2,0)"

'Sheets(1).Range("b2:b" & lr).Formula = "=vlookup(a2,sheets(3).range("C6:F11").value,2,false)"

Sheets(1).Range("b2:b" & lr).Formula = "=vlookup(sheets(1).range("a2"),sheets(3).range("C6:F11").value,2,false)"<strike></strike>

<strike></strike>
End Sub

 

[mart37]

Use no VBA ranges in a formula but Excel-ranges as: sheet3!C6:F11
The name RS is not know in your excelsheet only within VBA
Do not use value behind: Set RS

Set rs = ThisWorkbook.Sheets(3).Range("C6:F11")

Sheets(1).Range("b2:b" & lr).Formula= "=vlookup(A2,MyData,2,false)"
Sheets(1).Range("b2:b" & lr).Formula= "=vlookup(A2,rs,2,0)" 'this is wrong!
Sheets(1).Range("b2:b" & lr).Formula = "=vlookup(A2,sheet3!C6:F11,2,false)"
Sheets(1).Range("b2:b" & lr).Formula = "=vlookup(sheet1!A2,sheet3!C6:F11,2,false)"

 

[Mallesh23]

Hi Mart,

Thanks for the explaination doubt got cleared, still there is one I didn't got perfectly

how I use specific range of sheets in vlookup, to shorten the code. where it went wrong in my below code.

Please assist, what alternative I should have done in this case. Thanks.


Dim rs as range
Set rs = ThisWorkbook.Sheets(3).Range("C6:F11")
Sheets(1).Range("b2:b" & lr).Formula= "=vlookup(A2,rs,2,0)" 'this is wrong! 1
Regards,
Pranay

 

[mart37]

Dim rs As Range
Set rs = ThisWorkbook.Sheets(3).Range("C6:F11")
address = rs.address(External:=True)
address = Right(address, Len(address) - InStr(1, address, "]"))
Sheets(1).Range("B2:B" & lr).Formula = "=vlookup(A2," & address & ",2,0)"

 

[mart37]

Possible:

Dim rs As Range
Set rs = ThisWorkbook.Sheets(3).Range("C6:F11")
[FONT=Verdana]Sheets(1).Range("B2:B" & lr).Formula = "=vlookup(A2," & rs.address[/FONT](External:=True) & ",2,0)"

 

[Joe4]

Dim rs As Range
Set rs = ThisWorkbook.Sheets(3).Range("C6:F11")
address = rs.address(External:=True)
address = Right(address, Len(address) - InStr(1, address, "]"))
Sheets(1).Range("B2:B" & lr).Formula = "=vlookup(A2," & address & ",2,0)"

Recommendation (best practice): NEVER use reserved words (like the names of existing functions, properties, etc) as the name of your variables (or procedures)!
It can cause confusion, errors, unexpected results, etc.

So you really shouldn't use a variable named "address". Give it some other name, i.e. "address1" or " myaddress".