0

im getting en 424 error when im trying to run this code in vba :

Sub Procurar()
ThisWorkbook.Activate
Sheets("Sheet").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Call OpenWorkbook

For i = 1 To Selection.Rows.Count
    ID = Cells(i, 1).Value
    Cells(i, 6).Value = Application.WorksheetFunction.VLookup(ID, UnidadesWb.Sheets("a").Range("A:B"), 2, False)
    On Error Resume Next
Next i

End Sub


Sub OpenWorkbook()
Dim UnidadesWb As Workbook
Set UnidadesWb = Workbooks.Open("C:\Users\h1ckzz\Desktop\PetLand\Workbooks\NewUNIDADES6.xlsx")
End Sub

Error is on the :

Cells(i, 6).Value = Application.WorksheetFunction.VLookup(ID, UnidadesWb.Sheets("a").Range("A:B"), 2, False)

Can anyone help me please ? Ty

Henrique Mazetti Ferraz
  • 17
  • 6
  • Your code works without context! Please, read this: [Object required (Error 424)](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/object-required-error-424). Imagine, when you open a workbook, then this workbook is getting active... – Maciej Los Feb 28 '23 at 16:23
  • It would be beneficial to read up on variable scope. `UnidadesWb` within `OpenWorkbook` is not within the scope of `Procurar`. – BigBen Feb 28 '23 at 16:50
  • I still dont get it why the code is getting this Object Required error. Whats the solution ? Because when i opened the other workbook, i activate my main workbook back – Henrique Mazetti Ferraz Feb 28 '23 at 17:20
  • @BigBen now i get it. My code runs, instead of using another sub, i activate and set the other workbook into Procurar – Henrique Mazetti Ferraz Feb 28 '23 at 17:39
  • In other words, `OpenWorkbook` should be a `Function` returning a `Workbook`, not a `Sub`. – BigBen Feb 28 '23 at 17:40

0 Answers0