vba help on vlookup

[Peter_g]

Hi Team,

I am testing vlookup formula in VBA, I am facing couple of issues ,

1) I want to shorten my code for by giving a variable name for Thisworkbook.sheet2!A1:B7 and use it in formula.
2) Before reaching to formula, my macro is getting stuck on this line, :=> Set ws1 = wbk.Worksheets object doesn't support this property or method.

I am new in vba , please assist in correcting my syntax. Thanks.

Sub Test()
Dim wbk As Workbook
Dim ws1 As Worksheet
Dim ws As Worksheet
Dim lr As Long
Set wbk = Workbooks.Open(Sheet1.Range("B5").Value)   ' F:\varsha\Book2.xlsx
Set ws1 = wbk.Worksheets
For Each ws In wbk.Worksheets
    Select Case ws.Name
        Case "A", "B", "C"
            lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
            ws.Range("B2:B" & lr).Formula = "=VLOOKUP(A2,Thisworkbook.sheet2!A1:B7,2,false)"
    End Select
Next ws

Thanks.
Peter

[mana]

"=VLOOKUP(A2,'[" & ThisWorkbook.Name & "]sheet2'!A1:B7,2,false)"

[Peter_g]

Hi Team,

Thanks for Quick reply.

how to read this syntax [" & ThisWorkbook.Name & "] that bracket what it tells. and Symbol & in it.


Regards,
Peter

[mana]

Sub test()
    MsgBox ThisWorkbook.Sheets("Sheet2").Range("A1:B7").Address(False, False, xlA1, True)
End Sub

[Peter_g]

Hi mana,

Thanks your formula worked.

I have one more question is there any other option to get the result ,

if I want to use below statement in formula, where I am getting wrong in it.

Dim rng as range
Set rng = ThisWorkbook. Sheets("Sheet2").Range("A1:B7").value
"=VLOOKUP(A2,rng ,2,false)"

Thanks
Peter

[mana]

Option Explicit


Sub test2()
    Dim rng As Range
    Dim adr As String
    
    Set rng = ThisWorkbook.Sheets("Sheet2").Range("A1:B7")
    adr = rng.Address(False, False, xlA1, True)


   MsgBox "=VLOOKUP(A2," & adr & ",2,false)"
 
End Sub

[Peter_g]

Hi, mana....Your advised code works perfectly. Thanks for reviewing every time

Regards,
Mallesh

[p45cal]

If you want to use the named range rng in your formulae then instead of:
Set rng = ThisWorkbook. Sheets("Sheet2").Range("A1:B7")
try:
ThisWorkbook.Sheets("Sheet2").Range("A1:B7").name = "rng"
then you can use "=VLOOKUP(A2,rng ,2,false)"
With the added benefit that if you want to change the rng range, you can do so, and the formulae will update by themseleves.

[Peter_g]

Thank you so much, it worked. Thanks.