Error 91 Object variable or With block variable not set

Question

I have this code

Sub search()
    Dim dato As String
    Dim filallibre As String
    dato = InputBox("Type 1 Or 2?")
    filalibre = Sheets(dato).Range("A65000").End(xlUp).Row + 1
    If dato = "" Then Exit Sub
    Set buscado = ActiveSheet.Range("A1:A" & Range("A65000").End(xlUp).Row).Find(dato, LookIn:=xlValues, lookat:=xlWhole)
    If Not buscado Is Nothing Then
        ubica = buscado.Address
        Do
            buscado.EntireRow.Copy Destination:=Sheets(dato).Cells(filalibre, 1)
            filalibre = filalibre + 1
            Set buscado = ActiveSheet.Range("A1:A" & Range("A65000").End(xlUp).Row).FindNext(buscado)
        Loop While Not buscado Is Nothing And buscado.Address <> ubica
    End If
End Sub

I am getting error in my code when I run the macro, it works fine when I type "1" but when y put "2" it does the job which is moving the rows to the worksheet named "2" but it doesn't stops, seems like it stays looping until i press Esc then the message box with the error appears, the error appears to be in the line

Loop While Not buscado Is Nothing And buscado.Address <> ubica

how can i Solve this?

EDIT [6/7]

thanks a lot for your suggestions, I've tried some of your suggestions and seems like either i'm too noob for this VBA level or there is something completely wrong with my code, here I is the link to my excel file if anyone wants to test it and see if you can find the problem

Basically what the file does when I run the macro with Ctrl+Shift+K, is to ask as for either "1" or "2" then it identifies which rows have 1 or 2 in the column A and move those rows to the sheet 1 or 2 depending of what number I entered in the input box, it works well with "1" but not with "2"

Same result, i does the job but keeps doing the loop until i press Esc — user80566
– user80566, Commented Jun 7, 2019 at 16:07
hi. is dato a variable or the sheet name ? if its the sheet name it must be a string like sheets("dato"). good luck — Luis Curado
– Luis Curado, Commented Jun 7, 2019 at 16:13
hi, dato is a variable which takes the value you type in the input box — user80566
– user80566, Commented Jun 7, 2019 at 16:21
You need to use Option Explicit at the top of every module. The Variable you are using in Dim filallibre As String and filalibre = Sheets(dato).Range("A65000").End(xlUp).Row + 1 are not the same, not to mention you declared it as a string but are using it as a long. — K.Dᴀᴠɪs
– K.Dᴀᴠɪs, Commented Jun 7, 2019 at 16:24

Tim Williams · Accepted Answer · 2019-06-07 20:23:52Z

2

You cannot combine these two tests in one line:

Loop While Not buscado Is Nothing And buscado.Address <> ubica

If the first test fails then your code will still proceed to check the value of buscado.Address - it does not stop testing on the first False (ie. there is no "short-circuit" execution in VBA as there is in some other languages)

You can show this easily:

Dim c As Range

If c Is Nothing Or c.Address = "$A$1" Then Debug.Print "OK" '<< error

If c Is Nothing And c.Address = "$A$1" Then Debug.Print "OK" '<< error

EDIT: @SJR is correct - there should never be a case where FindNext fails to locate a cell. Once the initial If Not buscado Is Nothing test passes, all later FindNext will get a match, because Find always loops around when it hits the last cell.

I missed that the OP's error only occured when they hit "Esc", so in this case my answer (though technically sound as far as short-circuiting in VBA goes) does not address whatever is the underlying problem (that the OP's code gets stuck in a loop).

edited Jun 7, 2019 at 20:23

answered Jun 7, 2019 at 16:23

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

K.Dᴀᴠɪs Over a year ago

So Loop Until buscado Is Nothing Or buscado.Address = ubica

Tim Williams Over a year ago

In two separate tests: eg see stackoverflow.com/questions/12099878/vba-cell-highlighting/…

Tim Williams Over a year ago

@K.Dᴀᴠɪs - that has the same problem: neither And nor Or will short-circuit

SJR Over a year ago

Not sure that can be the problem because if buscado Is Nothing then the whole loop is bypassed completely, and nothing in the Do Loop is moving or deleting the found values. (See my comment and OP's response.)

Tim Williams Over a year ago

@SJR - you are not missing something: I was. See my update above and thanks for persevering on that ;-)

|

Collectives™ on Stack Overflow

Error 91 Object variable or With block variable not set

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related