VBA LongVar = range.find test if variable is empty

Question

I have the following code:

Dim wb As Workbook
Dim ws As Worksheet
Dim Test As String
Dim TestRow As Long
Dim LastRow As Long

Set wb = ThisWorkbook
Set ws = wb.Sheets(1)

Test = "Test"
LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row 
TestRow = ws.Range("B1:B" & LastRow).Find(What:=Test).Row
'This was entered after the first error
    If TestRow Is Empty Then
        ws.Range("B" & LastRow + 1) = Test
    End If

Without the If statement and Test is non-existent in my data I get the following error: 'runtime 91 object variable or with block variable not set'.

Next I'd like to test if TestRow is empty/zero I get a Type mismatch on TestRow. I've tried Is Nothing and various other options but I cant seem it to get it to work.

Any tips?

Answer 1

Try with this instead:

Dim wb As Workbook
Dim ws As Worksheet
Dim Test As String
Dim TestRow As Range
Dim LastRow As Long

Set wb = ThisWorkbook
Set ws = wb.Sheets(1)

Test = "Test"
LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
Set TestRow = ws.Range("B1:B" & LastRow).Find(What:=Test)

If TestRow Is Nothing Then
    ws.Range("B" & LastRow + 1) = Test
Else
    'Put code here if found Test (TestRow.Row is your row that it found it on)
End If

Find likes to return a Range so you need to Dim as a range and then Set the range.

Then we check if it found anything by the TestRow Is Nothing or Not TestRow Is Nothing.

Because you want the row then you would then use TestRow.Row for it's row number.