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
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