.find() triggers run-time error 91 even though all variables are set VBA possibly due to bad references

Question

I am writing code to create a template. This code populates a tab named "fullDistribution" from user-input on different tabs in the same wb. I have a working section of code that I wrote in a separate module (for testing) away from my master module. The code runs properly and executes completely when it is separate. When I pasted this section of code into my master module and ran it, I began receiving "Run-time error 91: object variable or with block variable not set" at the start of the newly-pasted code. I am not using any with blocks, and all of my variables are set. I made no changes in my code when I transferred it to my master module, and I carried over the new variables I created.

This is the selection of code that I wrote in a separate module:

Worksheets("bls2016").Activate
tcount = WorksheetFunction.CountA(Worksheets("detailedEntity").Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row))
acount = WorksheetFunction.CountA(Worksheets("detailedEntity").Range("K2:K7"))
Application.ScreenUpdating = False

Dim h As Integer
Dim f As Integer
Dim blstate As Range
Dim bl As Range
Dim state As Range
Dim deat As Range
Dim agje As Range
Dim e As Integer
Dim r As Integer
Dim ii As Integer

Set blstate = Worksheets("bls2016").Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
Set state = Worksheets("detailedEntity").Range("Q1")
Set deat = Worksheets("detailedEntity").Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
Set agje = Worksheets("detailedEntity").Range("L2:M" & Cells(Rows.Count, "M").End(xlUp).Row)


h = Activecolumn
f = Activerow
r = 2

x = 120
    For e = 1 To (acount * acount)
        blstate.Find(state).Select
        For ii = 1 To x
        'ccnt = acst.Offset(0, 1)
            ccgv = ActiveCell.Offset(0, 2)
            acem = ActiveCell.Offset(0, 5)
            Do While True
            vl1 = Application.IfNa(Application.VLookup(Worksheets("fullDistribution").Cells(r, 2), deat, 1, False), 0)
                If vl1 = 0 Then
                    Worksheets("fullDistribution").Cells(r, 4) = 0
                Else:
                    vl2 = Application.IfNa(Application.VLookup(Worksheets("fullDistribution").Cells(r, 1), agje, 2, False), 0)
                    If ActiveCell.Offset(0, 1).Value = "Unknown Or Undefined" Then
                        Exit Do
                    Else:
                        If vl2 = ccgv Then
                            Worksheets("fullDistribution").Cells(r, 4) = acem
                        ElseIf vl2 <> ccgv Then
                            Worksheets("fullDistribution").Cells(r, 4) = ActiveCell.Offset(x + 1, 5)
                        Else:
                            End If
                        End If
                    End If
                Exit Do
            Loop
            ActiveCell.Offset(f + 1, h).Select
        r = r + 1
        Next ii
    Next e

The error triggers at the line "blstate.find(state).select" which tells excel to look in a dynamic range that contains the names of states and select the first instance of the state to use as the Activecell. Again, this works when it's run outside of the main module.

I believe this has something to do with a reference area. When this runs alone and finishes, I have to have a specific worksheet activated for it to run properly. If my excel workbook is open to a different tab, it will not run. My main module too only executes properly if it is run on a specific worksheet/tab.

If need be, I can edit my post and provide my whole master code.

updated, my apologies. I meant at line "blstate.Find(state).select" — tlaw the outlaw, Jul 06 '17 at 20:12
If you will search this site for "vba .find 91" you'll find (likely) hundreds of people who have asked the same question like [this one](https://stackoverflow.com/questions/34485313/vba-find-last-row-fails-with-error-91). `blstate.Find(state)` isn't finding anything, so when it attempts to `.Select` there's nothing to select. — FreeMan, Jul 06 '17 at 20:21
See also the [Documentation](https://stackoverflow.com/documentation/vba/8917/vba-run-time-errors/27750/run-time-error-91-object-variable-or-with-block-variable-not-set). — FreeMan, Jul 06 '17 at 20:23
why wouldn't it find anything? I know that the value I'm searching for is in the range and have run the code in a separate module successfully. — tlaw the outlaw, Jul 06 '17 at 20:24
Find recalls all settings used in the last call (even if you use the GUI to perform the Find), so make sure you specify the settings you want when you call it via VBA. If you don't do that, it may not work as you expect.... — Tim Williams, Jul 06 '17 at 20:27
Also: see here for how to fix your select/activate approach: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros?rq=1 — Tim Williams, Jul 06 '17 at 20:30

score 2 · Answer 1 · answered Jul 06 '17 at 20:22

It may be a problem of not fully referencing sheets, eg amend your blstate line to

with Worksheets("bls2016")
 Set blstate = .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
end with

Then it might find the value and not error. You should look up how to use the Find method as your way is destined to cause you headaches.

score 0 · Accepted Answer · answered Jul 06 '17 at 21:11

blstate.Find(state).Select

Your code assumes that .Find finds what it's looking for. When Find doesn't find what it's looking for, the function returns Nothing, which is essentially a null object reference - and you can't make member calls on Nothing without getting run-time error 91.

Split it up:

Dim result As Range
Set result = blstate.Find(state)
If Not result Is Nothing Then
    result.Select 'questionable anyway, but that's another issue
Else
    MsgBox "Value '" & state & "' was not found in " & blstate.Address(External:=True) & "."
    Exit Sub
End If

As for why it's not finding what you're looking for, Tim Williams already answered that:

Find recalls all settings used in the last call (even if you use the GUI to perform the Find), so make sure you specify the settings you want when you call it via VBA. If you don't do that, it may not work as you expect.... – Tim Williams 42 mins ago

score 0 · Answer 3 · answered Jul 17 '17 at 15:36

My issue was very much related to incorrect referencing, however, I was able to resolve this issue by keeping the specific piece of code I was testing in a separate sub, and calling it from my main code, 'full distribution'.

Call test

'test' is the name of the sub with the tested code. This is a temporary fix to the solution, and if anyone struggles with referencing, try this.

.find() triggers run-time error 91 even though all variables are set VBA possibly due to bad references

3 Answers3