PDA

View Full Version : [SOLVED] vba help on vlookup



Peter_g
08-19-2017, 11:59 PM
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.: pray2::banghead:


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
08-20-2017, 12:24 AM
"=VLOOKUP(A2,'[" & ThisWorkbook.Name & "]sheet2'!A1:B7,2,false)"

Peter_g
08-20-2017, 12:40 AM
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
08-20-2017, 01:04 AM
Sub test()
MsgBox ThisWorkbook.Sheets("Sheet2").Range("A1:B7").Address(False, False, xlA1, True)
End Sub

Peter_g
08-20-2017, 01:28 AM
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
08-20-2017, 01:36 AM
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
08-20-2017, 01:43 AM
Hi, mana....Your advised code works perfectly:2jump:. Thanks for reviewing every time

Regards,
Mallesh

p45cal
08-20-2017, 02:13 AM
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
08-20-2017, 02:53 AM
Thank you so much, it worked. Thanks.