VBA for Excel throws "Object variable or with block variable not set" when there is no Object
Solution 1
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
Solution 2
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
Solution 3
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
Solution 4
This is an old old post - but I ran across it when I was having trouble figuring out why I suddenly could not import a PDF export into my excel sheet.
For me the problem was a row I was trying to match on was merged - did a simple unmerge for the entire sheet first and it worked like a charm.
'////// Select and open file
FieldFileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*") 'pick the file
Set frBook = Workbooks.Open(FieldFileName, UpdateLinks:=0, ReadOnly:=True, AddToMru:=False)
For Each mySheet In frBook.Worksheets
mySheet.Cells.UnMerge
Next mySheet
Related videos on Youtube
wardzin
Updated on March 11, 2020Comments
-
wardzin about 4 years
In my code, I have declared these variables:
Dim Field_Name, Datatype, row As Integer
Then, inside a For loop, I have this code:
Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column Datatype = Worksheets(i).UsedRange.Find("Datatype").Column row = Worksheets(i).UsedRange.Find("Field Name").row + 1
However, that code throws the "Object variable or with block variable not set" run-time error. According to the API, the Range.Column and Range.row property is a read-only Long. I have tried making the datatype of my variables to Long, but with no success. It would appear that VBA expecting me to do
Set Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column Set Datatype = Worksheets(i).UsedRange.Find("Datatype").Column Set row = Worksheets(i).UsedRange.Find("Field Name").row + 1
However, said variables are not objects, so doing that throws the "Object required" compile error.
Any help with this would be greatly appreciated. If you're not sure about how to fix it, then any workarounds or alternative ways to get the column number and row number of a cell would be greatly appreciated.
-
Wayne G. Dunn about 10 yearsChange your 'Dim' to three Dim's with only one variable name and type per Dim. Then try again. If still have error, then which line is it on? Reference: msdn.microsoft.com/en-us/library/office/…
-
Dmitry Pavliv about 10 yearsthe most likely issue of your error is that
Find
returnsNothing
, i.e. string"Field Name"
or"Datatype"
not found -
Ron Rosenfeld about 10 yearsIt would be good to see the rest of your code, as sometimes "unclosed" loops or with statements can cause that kind of error. But I would agree with simoco in that your Find is probably Finding nothing.
-
Ron Rosenfeld about 10 yearsAlso, once you get that error straightened out, you should rethink declaring your variables as type Integer. The Column should be OK, but if you are using a version of Excel 2007+, there are more than 32,768 rows; so if your row of interest is further down, you will get an overflow error.
-
Ron Rosenfeld about 10 yearsOne other critique: Dim Field_Name, Datatype, row As Integer only declares 'row' as Integer; the other two will be of type Variant. Look at help for information on the Dim statement.
-
JeopardyTempest over 5 yearsFor those catching up, I believe the issue was probably what Wayne commented... as I've made the same mistake in the past... should be Dim Field_Name as Integer, Datatype as Integer, row as Integer.
-
JeopardyTempest over 5 yearsAnd if it happens you are just perplexed by a general issue of "Object variable or with block variable not set" in general (since this is the first Google SE result for the error)... make very sure you've got that word SET to start the line the error comes up for, right at the front, before the variable receiving. 7yphoid indeed did this, but it may well be it's your error... as the obfuscated/arcane warning is typically a sign you're trying to assign a reference like a value (= isn't interchangeable in Excel in this way like it is many languages). You're passing "directions" not a value.
-
-
Siddharth Rout about 10 years+ 1 BirdsView: This should work. It might be an icing on the cake if you declare your variables and indent your code ;)
-
yngrdyn almost 8 yearsThank you so much for your answer, this hit in the right point. The problem was the Nothing thing.
-
Toby about 7 yearsThank you @matroid for pointing this out. I didn't even notice that the macro recorder added .Activate which caused the statement to fail always.
-
Mathieu Guindon almost 3 years@BigBen
Find("whatever").AnyChainedMemberCall
is actually where OP's error 91 is thrown; the first part here is partly off, the variable doesn't even get to see an assignment happening, because the error is raised as the RHS is being evaluated. OP's variable names are misleading, which isn't helping. Row numbers declaredAs Integer
is also a potential problem that should be called out; 16-bit integer data types haven't been able to fit the number of rows in a worksheet for a long time now. But with OP's declarations, wouldn'tSet DataType = ...
be a fully late-bound statement? -
BigBen almost 3 years
Set Datatype = Worksheets(i).UsedRange.Find("Datatype").Column
is an immediate error.Set
andColumn
don't belong in the same line. Also the suggestion toDim DataType As Variant
doesn't change anything from OP.Find
returns aRange
so the result ofFind
should be assigned to aRange
variable, not aVariant
. -
DaveP over 2 yearsI get the error without method chaining, rng.find("Doesn't exist") results in object not set.