Object Variable not Set in EXCEL VBA - VLOOKUP

Question

Private Sub CommandButton1_Click()
'
'Dims and sets
Dim intRows As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range
Dim srchres As Variant
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws1 = ThisWorkbook.Sheets("Sheet2")

'
'Determine number of rows in col A containing data
intRows = Application.CountA(Range("B:B"))
MsgBox intRows
For i = 1 To intRows
 srchres = Application.WorksheetFunction.VLookup(ws2.Range("B2"), ws1.Range("A1:1"), 1, False)
 Cells(i, 6).Value = srchres
Next i
End Sub

I am new to using VBA for excel. I am trying to do a Vlookup on Col1 in Worksheet1 with corresponding values in Col1 on Worksheet2 to return the values in col2 of Worksheet2. I get a run time error:

object variable not set.

Answer 1

VBA Excel VLookup

Adjust the values in the constants section to fit your needs.

The Code

Option Explicit

Private Sub CommandButton1_Click()

    ' First Sheet
    Const cSheet1 As Variant = "Sheet1"   ' First Sheet Name/Index
    Const cFirstR1 As Integer = 1         ' First Sheet First Row
    Const cRead As Variant = "A"          ' Read Column Letter/Number
    Const cRes As Variant = "F"           ' Result Column Letter/Number

    ' Second Sheet
    Const cSheet2 As Variant = "Sheet2"   ' Second Sheet Name/Index
    Const cFirstR2 As Integer = 2         ' Second Sheet First Row
    Const cFirstC2 As Variant = "A"       ' First Column Letter/Number
    Const cLastC2 As Variant = "B"        ' Last Column Letter/Number
    Const cLookup As Integer = 2          ' Lookup Column

    Dim ws1 As Worksheet  ' First Sheet
    Dim ws2 As Worksheet  ' Second Sheet
    Dim rng As Range      ' Lookup Range
    Dim LastR1 As Long    ' First Sheet Last Row
    Dim LastR2 As Long    ' Second Sheet Last Row
    Dim i As Long         ' First Sheet Row Counter

    With ThisWorkbook
        Set ws1 = .Sheets(cSheet1)
        Set ws2 = .Sheets(cSheet2)
    End With

    With ws1
        ' Calculate last row of First Sheet.
        LastR1 = .Cells(.Rows.Count, cRead).End(xlUp).Row
        ' Clear contents of Result Range (column).
        .Range(.Cells(cFirstR1, cRes), .Cells(LastR1, cRes)).ClearContents
    End With

    With ws2

        ' Calculate last row of Second Sheet.
        LastR2 = .Cells(.Rows.Count, cFirstC2).End(xlUp).Row
        ' Calculate Lookup Range.
        Set rng = .Range(.Cells(cFirstR2, cFirstC2), .Cells(LastR2, cLastC2))

        On Error Resume Next ' Ignore error if match not found.

        ' Loop through rows (cells) of First Sheet.
        For i = cFirstR1 To LastR1
            ' Write result to current cell in Result Column.
            ws1.Cells(i, cRes) = Application.WorksheetFunction.VLookup( _
                    ws1.Cells(i, cRead), rng, cLookup, False)
        Next

        On Error GoTo 0 ' Turn off ignore error.

    End With

    ' Release object variables
    Set rng = Nothing
    Set ws2 = Nothing
    Set ws1 = Nothing

End Sub