Vlookup type mismatch

Question

I'm trying perform a vlookup in VBA and the code doesn't seem to be getting cells value properly. The variables rowrng and colrng are being returned as #N/A

  Sub DosiDo()

'Declare Variables
Dim colnum As Long
Dim rownum As Long
Dim i As Integer
Dim rowrng As Variant
Dim colrng As Variant

'Set worksheets
Dim wb As Workbook
Dim ws As Worksheet
Dim newWs As Worksheet
Dim table1 As Range
Dim ws1 As String

Set wb = ActiveWorkbook
Set newWs = wb.Worksheets.Add
newWs.Name = "DosiDo"

    
    With Workbooks("210721-LeaveRecords.xlsm").Sheets("Sheet1")
            Set table1 = .Range(.Cells(2, 2), .Cells(7, 9))
    End With

    wsl = "AS Darwin"

    rowrng = Application.VLookup(ws1, table1, 7, False)
    colrng = Application.VLookup(ws1, table1, 8, False)
    
    newWs.Cells(i + 1, 4).Value = rowrng
    newWs.Cells(i + 1, 5).Value = colrng

End Sub

Answer 1

You dim ws1 wrong.

Change ws1 to wsl

  Sub DosiDo()

'Declare Variables
Dim colnum As Long
Dim rownum As Long
Dim i As Integer
Dim rowrng As Variant
Dim colrng As Variant

'Set worksheets
Dim wb As Workbook
Dim ws As Worksheet
Dim newWs As Worksheet
Dim table1 As Range
Dim wsl As String

Set wb = ActiveWorkbook
Set newWs = wb.Worksheets.Add
newWs.Name = "DosiDo"

    
    With Workbooks("210721-LeaveRecords.xlsm").Sheets("Sheet1")
            Set table1 = .Range(.Cells(2, 2), .Cells(7, 9))
    End With

    wsl = "AS Darwin"

    rowrng = Application.VLookup(ws1, table1, 7, False)
    colrng = Application.VLookup(ws1, table1, 8, False)
    
    newWs.Cells(i + 1, 4).Value = rowrng
    newWs.Cells(i + 1, 5).Value = colrng

End Sub