CODEWITHSUNDEEP
vbaexcel
Kai
Kai

Reputation: 780

Error code is 91 on Find

This may seem like a simple question but Im very new to VBA and Im not sure why I'm receiving the error.

Dim c As String
c = Sheet2.Range("B3:B54").Find("NLwk01")

Error code is 91: Object variable or With block variable not set.

I thought I should've maybe used cells instead of range, but that gives another error with

Error code 5: Invalid procedure call or argument.

Upvotes: 3

Views: 108

Answers (1)

Alexander Bell
Alexander Bell

Reputation: 7918

As it was mentioned in comment thread, Excel VBA Find() function returns the Range object. Therefore, pertinent to you particular example, it could be coded as in the following sample snippet:

Sub FindRowIndex()
    Dim c
    Dim rowIdx As Integer
    Dim cellValue As String

    'return Range object if found
    Set c = Sheet2.Range("B3:B54").Find("NLwk01")

    If Not c Is Nothing Then
        'return the row index (shown as an example)
        rowIdx = c.Row
        'return the same string used as search criterion "NLwk01"
        cellValue = c.Value
    End If
End Sub

Pertinent to your case search area ("B3:B54") the rowIdx can be declared As Integer; for extended area you may use Long.

Also, as mentioned in comments thread, you may declare: Dim c As Range.

Hope this may help.

Upvotes: 1

Related Questions