CODEWITHSUNDEEP
excelvba
Ace16150s
Ace16150s

Reputation: 97

Function to find row based on cell value

I have the following code to find the row where a certain value resides, however it keeps debugging with

Error 91 "Object variable or With block variable not set"

Which is weird because I use the same structure to find a row before this procedure and it works.

wbs.Activate
    Cells.Find(What:="Name", After:=wbs.Range("A1"), LookIn:=xlFormulas, _
      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=True, SearchFormat:=False).Activate
    NameRow = ActiveCell.Row

Upvotes: 0

Views: 9581

Answers (2)

Ace16150s
Ace16150s

Reputation: 97

Apparently there was a space after "Name", so I should have been looking for "Name ". Revised the search and it worked the way I had it but thank you @CallumDA for your clean answer. So my problem is that it was not able to find my lookup variable which in turn meant it could not activate the found cell resulting in the Error 91! Much appreciated for your quick reply.

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

Your only problem is that when you don't have "Name" on your worksheet .Find returns Nothing rather than a Range object. You then get an error because you are trying to use .Activate on Nothing.

The solution

There is no need to use Activate and ActiveCell, just define your variables well and use them! Here's a working example:

Sub test()
    Dim wks As Worksheet
    Dim r As Range
    Dim rowNumber As Long

    Set wks = ThisWorkbook.Worksheets("sheet1") 'update for your worksheet name

    '.Find returns a Range object or Nothing    
    Set r = wks.Cells.Find(What:="Name", LookAt:=xlWhole) 

    If Not r Is Nothing Then
        rowNumber = r.Row
    End If

    MsgBox rowNumber
End Sub

Upvotes: 4

Related Questions