VBA Error 91

[kjuran]

I am creating a VBA for the VLOOKUP function. I want to be able to capture a name and grade from Book 2 and have it display on Book1 based on the ID number, however, I keep getting error 91 even though I initialized the sht variable. Here is the code:

Sub vlookup1()

'Initializing workbook connection
Dim PathName As String
Dim FileName As String
Dim wkbk As Workbook
Dim sht As Worksheet
PathName = "K:\Kayla Data Auto-Population\VBA Experiment\"
FileName = "Book 2.xlsx"
Set wkbk = Workbooks.Open(PathName & FileName)
Set sht = wkbk.Worksheets("Sheet1") <- where error is occurring

'Initializing Variables
Set myrange = sht.Range("A:C")
Set ID = Range("A2")
Set Name = Range("B2")
Set Grade = Range("C2")

'Initializing VLOOKUP function
Name.Value = Application.WorksheetFunction.VLookup(ID, myrange, 2, False)
Grade.Value = Application.WorksheetFunction.VLookup(ID, myrange, 3, False)

End Sub

 

[JackDanIce]

Is the sheet called Sheet 1 in "Book 2.xlsx"?
That type of error suggests it can't find the object with that name

This worked for me:

Sub Map_Values()

    Const PathName As String = "C:\Users\Win\OneDrive\Desktop\"
    Const FileName As String = "Book 2.xlsx"
    Const SheetName As String = "Sheet 1"
    
    With [A2:C2]
        Workbooks.Open PathName & FileName
        .Offset(, 1).Resize(, 2).Value = ActiveWorkbook.Sheets(SheetName).[A:A].Find(.Cells(1, 1).Value, LookIn:=xlFormulas, lookat:=xlPart).Offset(, 1).Resize(, 2).Value
    End With
    
    ActiveWorkbook.Close False
    
End Sub

 

[kjuran]

Is the sheet called Sheet 1 in "Book 2.xlsx"?
That type of error suggests it can't find the object with that name

This worked for me:

Sub Map_Values()

    Const PathName As String = "C:\Users\Win\OneDrive\Desktop\"
    Const FileName As String = "Book 2.xlsx"
    Const SheetName As String = "Sheet 1"
   
    With [A2:C2]
        Workbooks.Open PathName & FileName
        .Offset(, 1).Resize(, 2).Value = ActiveWorkbook.Sheets(SheetName).[A:A].Find(.Cells(1, 1).Value, LookIn:=xlFormulas, lookat:=xlPart).Offset(, 1).Resize(, 2).Value
    End With
   
    ActiveWorkbook.Close False
   
End Sub

Yes, it is definitely Sheet1.
Are you saying I use the Const function and have the variable sht be a string

 

[JackDanIce]

I meant Sheet 1, with a space between 'Sheet' and '1'

Nope, just one VBA suggestion to try, different ways of coding actions/using variables/syntax etc.

Try starting again and record a macro that copies your actions, e.g.
Start with the workbook open that needs the code
File->Open->Book 2.xlsx
Switch back to the first workbook and type in the formula "=VLOOKUP(A2," then select the sheet, then the table in Book 2, add rest of the parameters and hit enter
Stop the recorder and then review the resulting code, it should correctly pick up the variable names and then compare that to your own.

 

[kjuran]

While that is a brilliant idea, my record macros button seems to be grayed out.

 

[Joe4]

While that is a brilliant idea, my record macros button seems to be grayed out.

That often means that you are in the middle of VBA code that you have not run to completion or cancelled (i.e. you are in "break" mode, in the middle of VBA code, or haven't closed out of the error).

 

[JackDanIce]

Or Helpdesk response: "Have you tried restarting your application/PC?"

 

[kjuran]

I restarted it, which allowed me to record macros but the formatting of the code is wildly different from what I had