VBA for Excel throws "Object variable or with block variable not set" when there is no Object

Even though this is an old question, I'd like to say something too.

I had the same problem to get this error while using the .Find method. I came to this question and so others will do the same.

I found a simple solution to the problem:

When Find does not find the specified string it returns Nothing. Calling anything directly after Find will lead to this error. So, your .Column or .row will throw an error.

In my case I wanted an Offset of the found cell and solved it this way:

Set result = Worksheets(i).Range("A:A").Find(string)    If result Is Nothing Then        'some code here    ElseIf IsEmpty(result.Offset(0, 2)) Then        'some code here    Else        'some code here    End If

Simplified answer:

Your .Find call is throwing the error.

Simply adding "Set " to that line will address the problem. i.e...

Set Datatype = Worksheets(i).UsedRange.Find("Datatype").Column

Without "Set," you are attempting to assign "nothing" to a variable. "Nothing" can only be assigned to an object.

You can stop reading here unless you would like to understand what all the other (valid, worthwhile) fuss was about your code.

To paraphrase all of the (warranted) code critiquing, your Dim statement is bad. The first two variables are not being "typed" and end up as variants. Ironically, this is why the solution I just described works.

If you do decide to clean up that Dim statement, declare DataType as a variant...

Dim DataType as variant

What about the below code:

    For i = 1 to 1 ' change to the number of sheets in the workbook    Set oLookin1 = Worksheets(i).UsedRange    sLookFor1 = "Field Name"    Set oFound1 = oLookin1.Find(What:=sLookFor1, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)    If Not oFound1 Is Nothing Then    Field_Name = oFound1.Column    RRow = oFound1.Row +1' code goes here    Else    Msgbox "Field Name was not found in Sheet #" & i    End If    Set oLookin2 = Worksheets(i).UsedRange    sLookFor2 = "Datatype"    Set oFound2 = oLookin2.Find(What:=sLookFor2, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)    If Not oFound2 Is Nothing Then    DataType = oFound2.Column' code goes here    Else    Msgbox "Datatype was not found in Sheet #" & i    End If    Next i