CODEWITHSUNDEEP
vbaexcel
Alex Cavanaugh
Alex Cavanaugh

Reputation: 435

Error "object variable or with block variable not set" when searching through code

I'm trying to write a subroutine that searches through a range of cells, and returns the column number of the first cell with the specified value. This is what I have so far:

Dim StartCol As Long
Dim rngSearch As Range
Dim rngFound As Range

USedCol = ActiveWorkbook.Sheets("...").UsedRange.Columns.Count

    Set rngSearch = Range(Cells(6, 2), Cells(6, USedCol))
    Set rngFound = rngSearch.Find(What:="Jun", LookIn:=xlValues, LookAt:=xlWhole)
    StartCol = rngFound.Column

Unfortunately this gives me the error "object variable or with block variable not set". The error must be coming from a block variable not being set since I'm not using any with statements. I've used almost this exact same line of code in other programs and it has worked perfectly. I'm not sure what I'm missing here. I greatly appreciate any help, thanks.

Also, when I debug, the line that gets highlighted is 

StartCol = rngFound.Column

Let me know if you need any other information.

Upvotes: 0

Views: 249

Answers (1)

Luke Cummings
Luke Cummings

Reputation: 367

This would happen if the search was unsuccessful, from MSDN:

Range.Find Method (Excel)

...

This method returns Nothing if no match is found.

Link: https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

You can test for this:

If rngFound Is Nothing Then
    'Code to handle not found case
Else
    StartCol = rngFound.Column
End If

Upvotes: 2

Related Questions