Runtime Error 424 generated when activating file identified in cell

[Ochimus]

Attached ORDER file has DropLists of Products and Suppliers in Cols B - C

Attached Supplier file contain PRODUCT and COST columns.(Real folder has several different supplier files).

User selects a Product and then a Supplier.

Worksheet change finds the relevant Supplier file, the Product selected in the ORDER file, and copies the Cost from Col B of the SUPPLIER into Col D of the ORDER file.

Option Explicit
Dim a As Long
Dim KeyCells As Range
Dim wkb As Workbook
Dim wks As Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)

'Find last row with data in Col C
    
    With ActiveSheet
    a = .Cells(.Rows.Count, "C").End(xlUp).Row

'Set Cells that will cause an alert when they are changed.
    Set KeyCells = .Range("C2:C" & a)
    
'Exit Macro if more than one cell is chosen
    If Target.Count > 1 Then Exit Sub
    
'Run Macro if one cell in Col C changes
    If Not Application.Intersect(KeyCells, .Range(Target.Address)) Is Nothing Then

'Set workbook "selected" in the target cell, and add ".xls" as extension ***throws a "RuntIme Error 424 - Object Required"***
    Set wkb = Sheet1.Range(Target.Address) & ".xls"
        
'Set sheet 1 of that workbook as the sheet to check
    Set wks = wkb.Sheet1
    
'Find the relevant file for whichever Supplier is selected in the Target cell, find the Product in the Supplier Col A matching Col B on the Target row, copy the value from Supplier Col B into Col D of the Target row in the ORDERS file
    .Range(Target.Address).Offset(0, 1) = Application.WorksheetFunction.Index(wks.Range("B:B"), Application.WorksheetFunction.Match(.Range(Target.Address).Offset(0, -1), wks.Range("A:A"), 0))
    
    End If
    
    End With
    
End Sub

All solutions, suggestions and alternatives welcome as ever.

Ochimus

[JLGWhiz]

I have edited the code but it might not help very much. The line in red font is effective only if the workbook is already open. If it is not already open then it would need to be like this:

Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & Target.Value & ".xls"

to open the workbook and set the object variable. Also, I note the file extension is .xls but your sample files are posted as .xlsx and .xlsm, so that might also need editing.

Private Sub Worksheet_Change(ByVal Target As Range)
'Find last row with data in Col C
    With ActiveSheet
    a = .Cells(.Rows.Count, "C").End(xlUp).Row
    'Set Cells that will cause an alert when they are changed.
    Set KeyCells = .Range("C2:C" & a)
        'Exit Macro if more than one cell is chosen
        If Target.Count > 1 Then Exit Sub
        'Run Macro if one cell in Col C changes
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
            'Set workbook "selected" in the target cell, and add ".xls" as extension ***throws a "RuntIme Error 424 - Object Required"***
            Set wkb = Target.Value & ".xls"
            'Set sheet 1 of that workbook as the sheet to check
            Set wks = wkb.Sheet1
            'Find the relevant file for whichever Supplier is selected in the Target cell, find the Product in the Supplier Col A matching Col B on the Target row, copy the value from Supplier Col B into Col D of the Target row in the ORDERS file
            Target.Offset(0, 1) = Application.WorksheetFunction.Index(wks.Range("B:B"), Application.WorksheetFunction.Match(Target.Offset(0, -1), wks.Range("A:A"), 0))
        End If
    End With
End Sub

P.S. Using Range(Target.Address) is a redundant expression. Target is a range, so that is all you need when referring to that range.

[Ochimus]

JLGWhiz,

Many thanks for response and suggestions.

The original ORDER file had a macro that opened the various supplier files when it launched, so the Supplier file would have been open before the Change macro fired, but I removed that and used your amended line to open whichever Supplier file was in the Target cell.

It didn't work whilst the folder holding the files was in the Cloud.

So I copied them to the "C" drive, opened ORDERS and changed the Target.
Supplier file now opens, but setting the Worksheet as "Sheet 1 in the Supplier file" throws a 'Runtime Error 438 - Object doesn't support this property or method'.

Simply can't see where the Syntax is wrong?

Ochimus

[JLGWhiz]

I flunked mental telepathy, so you will need to post the code as you currently have it so I can take a look at it.

[Ochimus]

JLGWhiz,

With both files on the machine, not in the Cloud, it opens the Supplier file, but then fails to "set" Sheet1, so the Index/Match doesn't link to anything.

Option Explicit
Dim a As Long
Dim KeyCells As Range
Dim wkb As Workbook
Dim wks As Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
'Find last row with data in Col C
    With ActiveSheet
    a = .Cells(.Rows.Count, "C").End(xlUp).Row
    
'Set Cells that will cause an alert when they are changed.
    Set KeyCells = .Range("C2:C" & a)
        
'Exit Macro if more than one cell is chosen
        If Target.Count > 1 Then Exit Sub
        
'Run Macro if one cell in Col C changes
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
            
'Open workbook "selected" in the target cell (with ".xls" added as extension)
        Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & Target.Value & ".xlsx")
                    
'Set sheet 1 of that workbook as the sheet to check -  ***throws a 'Runtime Error 438 - Object doesn't support this property or method'***.

        Set wks = wkb.Sheet1
            
'Find the relevant file for whichever Supplier is selected in the Target cell, find the Product in the Supplier Col A matching Col B on the Target row, copy the value from Supplier Col B into Col D of the Target row in the ORDERS file
        Target.Offset(0, 1) = Application.WorksheetFunction.Index(wkb.shhet1.Range("B:B"), Application.WorksheetFunction.Match(Target.Offset(0, -1), wks.Range("A:A"), 0))
            
        wkb.Close
        
        End If
    
    End With
    
End Sub

Ochimus

[JLGWhiz]

There was a typo in your worksheet function statement. I changed that statement to VBA equivalent and also changed Sheet1 code name to Sheets("Sheet1") sheet name. Sheet 1, Sheets(1) and Sheets("Sheet1") can be three different sheets in the same workbook because of the way Excel is designed. See if the code below will work for you.

Option Explicit
Dim a As Long
Dim KeyCells As Range
Dim wkb As Workbook
Dim wks As Worksheet, fn As Range

Private Sub Worksheet_Change(ByVal Target As Range)
'Find last row with data in Col C
    With ActiveSheet
    a = .Cells(.Rows.Count, "C").End(xlUp).Row
'Set Cells that will cause an alert when they are changed.
    Set KeyCells = .Range("C2:C" & a)
'Exit Macro if more than one cell is chosen
        If Target.Count > 1 Then Exit Sub
'Run Macro if one cell in Col C changes
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then
'Open workbook "selected" in the target cell (with ".xls" added as extension)
        Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & Target.Value & ".xlsx")
'Set sheet 1 of that workbook as the sheet to check -  ***throws a 'Runtime Error 438 - Object doesn't support this property or method'***.

        Set wks = wkb.Sheets("Sheet1")
'Find the relevant file for whichever Supplier is selected in the Target cell, find the Product in the Supplier Col A matching Col B on the Target row, copy the value from Supplier Col B into Col D of the Target row in the ORDERS file
    Set fn = wks.Range("A:A").Find(Target.Offset(, -1).Value, , xlValues)
        If Not fn Is Nothing Then
            Target.Offset(, 1) = fn.Offset(, 1).Value
        Else
            Beep
            MsgBox "No match found"
        End If
        wkb.Close False
        End If
    End With
End Sub

[Ochimus]

JLGWhiz

Many thanks for the suggested changes, which almost "did the trick".

Thanks to your advice, the attached amended Code works perfectly, opening the correct Supplier file. posting the correct value, and closing the Supplier file at the end.

Option Explicit
Dim a As Long
Dim KeyCells As Range
Dim wkb As Workbook
Dim wks As Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.ScreenUpdating = False

    Application.EnableEvents = False
    
'Find last row with data in Col C
    With Sheet1
    a = .Cells(.Rows.Count, "C").End(xlUp).Row

'Set Cells that will cause an alert when they are changed.
    Set KeyCells = .Range("C2:C" & a)

'Exit Macro if more than one cell is chosen
        If Target.Count > 1 Then Exit Sub

'Run Macro if one cell in Col C changes
        If Not Application.Intersect(KeyCells, Target) Is Nothing Then

'Open workbook "selected" in the target cell (with ".xls" added as extension)
        Set wkb = Workbooks.Open(ThisWorkbook.Path & "\" & Target.Value & ".xlsx")

'Set sheet 1 of that workbook as the sheet to check
        Set wks = wkb.Sheets("Stocklist")

'Find the relevant file for whichever Supplier is selected in the Target cell, find the Product in the Supplier Col A matching Col B on the Target row, copy the value from Supplier Col B into Col D of the Target row in the ORDERS file
        Target.Offset(0, 1) = Application.WorksheetFunction.Index(wks.Range("B:B"), Application.WorksheetFunction.Match(Target.Offset(0, -1), wks.Range("A:A"), 0))
        wkb.Close
        
        End If
    
    End With
    
    Application.EnableEvents = True

    Application.ScreenUpdating = True

End Sub

Can now mark this as Solved.

Ochimus

[JLGWhiz]

Glad you got it worked out,
Regards, JLG