Run-time error '91'. Object variable or With block variable not set

Question

I have an Excel Macro which has been running fine for over a year, until yesterday I got an error Run-time error '91'. Object variable or With block variable not set. I can confirm all sheets are present. Appreciate any advice to troubleshoot. I have highlighted the line where the error occurs.

Sub Insert_Last_to_Input()
    Dim dateBaltic As Date
    Dim rngFFA As Range
    Dim longLastRowNo As Long
    Dim longBaltic, longFEI, longMB As Long
    
    dateBaltic = Sheets("Input").Range("B2").Value
    
    Sheets("Baltic FFA").Select
    
    With ActiveSheet
        longLastRowNo = .Range("A:A").Find(What:=dateBaltic, LookIn:=xlValues).Row
        '**Error above:"Run-time error '91'. Object variable or With block variable not set"**
        Set rngFFA = .Range("B" & longLastRowNo & ":F" & longLastRowNo)
    End With
End Sub

You need to check if the `.Find` returned something or not. [Here](http://www.siddharthrout.com/index.php/2018/01/05/find-and-findnext-in-excel-vba/) is an example. Check out **Section 1** in that link `If Not aCell Is Nothing Then` BTW such questions have been asked umpteen times before in stackoverflow. — Siddharth Rout, Oct 14 '20 at 05:07
Declaring multiple variables in one line of e.g. type `Long` is done like this: `Dim longBaltic As Long, longFEI As Long, longMB As Long`. In your code the first two are declared as `Variant`. — VBasic2008, Oct 14 '20 at 08:43

score 0 · Answer 1 · answered Oct 14 '20 at 04:39

0

The problem seems is due to cannot "Find" a text, so it cannot return its row to you. That is - the text in the "Input" sheet cell "B2" cannot be found in active sheet column A.

answered Oct 14 '20 at 04:39

Victor19

97
8

score 0 · Answer 2 · answered Oct 14 '20 at 06:41

Thank you all. I found the answer but it is weird indeed.

Firstly the problem lies with ".Find". Excel is unable to find the value I want because the column width is too small and the full text cannot be displayed! See below picture 1.

In the above, the correct value for longLastRowNo is 213. So what I did was to increase the column width so the full text can be displayed. See below picture 2.

Notice when I widened the column width, ".Find" was able to find the correct row no and assign it to longLastRowNo, i.e. 213.

So problem is solved. Thank you very much to all!

score 0 · Answer 3 · answered Oct 14 '20 at 08:35

Find Method in Trouble

Your problem seems to be solved but deep inside you must know that it isn't. You can use the following quick fix:

longLastRowNo = .Range("A:A").Find(What:=dateBaltic, LookIn:=xlFormulas).Row

But I would strongly recommend that you use a correct approach, e.g.:

With WorkSheets("Baltic FFA")
    Dim cel As Range
    Set cel = .Range("A:A").Find(What:=dateBaltic, _
                                 LookIn:=xlFormulas, _
                                 LookAt:=xlWhole)
    If Not cel Is Nothing Then
    ' Cell found.
        longLastRowNo = cel.Row
        Set rngFFA = .Range("B" & longLastRowNo & ":F" & longLastRowNo)
    Else
    ' The cell could not be found.
        MsgBox "Could not find..."
        Exit Sub
    End With
End With

making the line Sheets("Baltic FFA").Select redundant (not needed).

Read about how to avoid using Select in this legendary post.

Run-time error '91'. Object variable or With block variable not set

3 Answers3

Find Method in Trouble