CODEWITHSUNDEEP
vbaexcel
Aaa
Aaa

Reputation: 219

Excel VBA object variable type mismatch

I am trying to determine the column number of a header in an Excel spreadsheet using the following VBA code:

Dim aCell As Range
Set aCell = Sheet1.Rows(1).Find(What:="ContractDescription", LookIn:=xlValues, _
 LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
 MatchCase:=False, SearchFormat:=False)

Then the following line of code gives an error:

MsgBox (aCell.Column)

It gives the run-time error 91: "Object variable or With block variable not set".

According to posts that I have read on the internet, this error occurs when you are trying to assign a value of one type to a variable that has been declared as a different type.

However, I am trying to understand why I am getting this error here since aCell is a range object and I am simply taking aCell.Column, but when I do Range("A1").Column I do get the valid 1 printed on the screen.

So what may be causing the problem here?

Upvotes: 0

Views: 1168

Answers (2)

user2480047
user2480047

Reputation:

The reason why you get this error is because aCell is null (no match was found). Before using a range from a Find method (or equivalent, where the range might be Nothing), you have to set a condition on the lines of:

If (Not aCell Is Nothing) Then
  MsgBox aCell.Column
End If

Upvotes: 2

Alex K.
Alex K.

Reputation: 175766

If .Find fails it will return & set aCell to Nothing so attempting to access any instance members like .Column will raise that error.

To guard against this;

if not aCell Is Nothing
  MsgBox aCell.Column
else
  MsgBox "not found"
end if

Upvotes: 3

Related Questions