VBA Run-Time error 91 "Object variable or with block variable not set"

Question

I have been struggling with this one for a while with no success. I wrote a code to import some data from a database, with the added problem they have different formats. So I wrote a section which would go through the other base and bring data back on the right order (that is not the entire code. I have done all the proper set statements for the objects):

Dim wb_atual, wb_verificar As Workbook
Dim sheet_atual, sheet_verificar As Worksheet
Dim choice, lin_max, lin_b_max, col, col_id, col_max, i, j, k As Integer
Dim is_equal As Boolean

        For i = 1 To col_max
            col = wb_verificar.Sheets(sheet_verificar.Name).Range("1:1").Find(wb_atual.Sheets(sheet_atual.Name).Cells(2, i).Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column
            sheet_atual.Cells(j, i).Value = sheet_verificar.Range(sheet_verificar.Cells(j - 1, col), sheet_verificar.Cells(j - 1, col)).Value
            lin_b_max = lin_b_max + 1
        Next i

That is the block that caused the error, specifically the "col" variable line. What that line does is to look for a column header from a worksheet in another one, and stores the number of the column. That loop works fine for i=1 and 2, but at the third iteration I get the error.

I am assuming, this isn't all of your code and somewhere there are "set" statements for your workbook and worksheet objects. The error message would point to the fact that one of those objects isn't "set". So it looks like either missing a set statement, or the "set" has failed due to the object you are trying to assign to it not existing, though if that were the case, I would have expected it to error within the set statement itself. Is the full code to large to post, can you confirm where the Set statements happen, is there other relevant logic that might affect them? — Mark Moore
Yes the full code is indeed a bit long. However, all of the workbook and worksheet variables have been set before, which is confirmed by the fact this for loop work fine on the first two iterations. The rest of the code is pretty modular, with little else affecting this loop. Since the error occurs between loops, all other variables are constant. — André Muraro
You should try to isolate your problem, and produce a minimal working example. Posting code snippets, that do not reproduce the error are generally not going to entice many people to assist with your problem. The best guess, from the information you have provided is that you are missing a set keyword, somewhere in the code unseen, or your Find statement is not finding what you have asked of it. — Rusan Kax
I have realized what the problem is. It seems the .find method was bugging out on one specific cell, although I don´t know why. Worked around it with a custom search function. And thank you for the tip, Rusan, I will pay more attention to reproducibility in the future. — André Muraro
@AndréMuraro, congratulations on overcoming your problem. You should post your own answer and accept it. That way future users can know very quickly and easily without having to read all the comments. The other good reason is that people trying to PROVIDE help, (like me, right now) don't have to read through all the comments to find out you don't really need help. Remember this post will be up for a long time. It will be just sitting in the Unanswered Questions, read over and over. — peege

Gary's Student Gary's Student · Accepted Answer · 2014-12-23T19:47:16

you need to Set wb_verificar to a value between the Dim statement and any statements that use the Object.

There may be other errors.

VBA Run-Time error 91 "Object variable or with block variable not set"

2 Answers