VBA Error "Type Mismatch"

[Guest]

I've run this many times with no errors. Yet now i'm getting a type mismatch
error. Could it be because I've already run this once in this particular
document? Or is it something else. I added a colum to the worksheet, as
well as a lookup table. Would the lookup table be screwing me up? I've
valued my results so there should be no formulas left. Any help would be
appreciated!

Jennifer

Sub ordinate()
Dim r As Range
For Each r In ActiveSheet.UsedRange
If LCase(r.Value) Like "*total*" Then
r.EntireRow.Interior.ColorIndex = 36
End If
Next
End Sub

 

[Trevor Shuttleworth]

Jennifer

the code looks OK ... not much to go wrong really. Very simple test worked
OK but then I haven't got your worksheet/data to play with. Formulae don't
seem to be a problem.

Suggest you run it again and, when it fails, got to Debug it.

In the Immediate Window, type:

?r.address and press enter to get the address of the cell being checked.
Then you can look at what is in the cell to narrow down the problem. Sorry
I can't be more specific.

Regards

Trevor

 

[Earl Kiosterud]

Jennifer,

If you have any cells that yield an error (DIV/0, NAME?, etc.), you can get
this error. Look in hidden columns, and such.

--
Earl Kiosterud
www.smokeylake.com

Never try to teach a pig to sing. It wastes your time, and just annoys the
pig.

 

[Gord Dibben]

Jennifer

Earl is probably correct with his guess.

Change the code to this which will ignore errors if that's your wish.

Sub ordinate()
Dim r As Range
For Each r In ActiveSheet.UsedRange _
.SpecialCells(xlConstants, xlTextValues)
If LCase(r.Value) Like "*total*" Then
r.EntireRow.Interior.ColorIndex = 36
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Another way around the problem that Earl describes is to use .Text instead of
..Value:

Sub ordinate()
Dim r As Range
For Each r In ActiveSheet.UsedRange
If LCase(r.Text) Like "*total*" Then
r.EntireRow.Interior.ColorIndex = 36
End If
Next r
End Sub