vlookup , run time error,438, object doesnt support

[Mallesh23]

Hi Team,

I got stuck below in highlighted Bold line. please assist.

getting object doesn't support error. Actually I want to open a existing workbook, by pasting its file path in

sheet1.range("b5").value (i.e MacroBook). after that I want to set it and would like to use the variable in looping.

plz check and assist. 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)
Set ws1 = wbk.Worksheet



For Each ws In 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
End Sub

 

[mart37]

It is better to use in B5: path + filename + extension

wbk.Worksheet is missing sheetname:Set ws = wb.Worksheets(1)
Set ws = wb.Worksheets("Sheet1")

 

[Mallesh23]

hi,
Thanks for you quick reply.

wbk.Worksheet is missing sheetname:Set ws = wb.Worksheets(1)
Set ws = wb.Worksheets("Sheet1")

if I amend above, Can that works for my each loop statement. because now ws means (sheet1) and the loop will run only for sheet (1) I think.

I want the loop to run on sheets {1,2,4}

Pls share some more tips on this . Thanks.


Regards,
Pranay

 

[Norie]

Pranay

Do you need this?

Set ws1 = wbk.Worksheet

If you want to loop through sheets in the workbook that the code opens try this.

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

By the way, this will also fail because of Thisworkbook.

 "=VLOOKUP(A2,Thisworkbook.sheet2!A1:B7,2,false)"

You need to change Thisworkbook to the actual workbook name.

 

[Mallesh23]

Hi Team,

I have two workbook, One is my Macro, (thisworkbook) and another is in ' F:\varsha\Book2.xlsx

I want open a excel file (F:\varsha\Book2.xlsx) and run a vlookup formula . my criteria in vlookup is Thisworkbook.sheet2!A1:B7,2

The file get open successfully with Set wbk = Workbooks.Open(Sheet1.Range("B5").Value)
But on this line Set ws1 = wbk.Worksheet my macro gets stuck, error message Object doesn't support this property or method pop up.

But if I delete this line (Set ws1 = wbk.Worksheet). my macro runs but its not showing the result get NA result.
My data is not remain constant in this. please assist whats going wrong, I am new in vba facing issue in it.



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) '
Set ws1 = wbk.Worksheet

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

 

[mart37]

I used the indexnumbers of the sheets.
It is better to use the names.

Dim wbkSource As Workbook, wbkTarget As Workbook
Dim ws1 As Worksheet
Dim ws As Worksheet
Dim lr As Long
Set wbkSource = ActiveWorkbook
Set wbkTarget = Workbooks.Open(wbkSource.Sheets(1).Range("B5").Value)
For Each ws In wbkTarget.Worksheets
    Select Case ws.Index
        Case 1, 2, 4
            lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
            ws.Range("B2:B" & lr).Formula = "=VLOOKUP(A2,[" & wbkSource.Name & "]Sheet2!$A$1:$B$7,2,false)"
    End Select
Next ws

 

[Mallesh23]

Hi Mart,
Your advised code works perfectly

. Thanks for reviewing every time

Regards,
Pranayg23