probably simple - type mimatch error 13

[PBcorn]

I want to run several consective tests on "curcell". Select case seemed less
messy than nested if-then. However I am getting the above error on the **
line. Help appreciated


Dim curcell As Range
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets

For Each curcell In sht.UsedRange.Cells

Select Case curcell.Column
Case Is <> 1
Select Case curcell.Row
Case Is <> 1
Select Case TypeName(curcell.Offset(-1, 0))
**Case Is = "String" Or "Date"
Select Case TypeName(curcell.Offset(0, -1))
Case Is = "String" Or "Date"
Select Case curcell.Offset(-1, 0).Value
Case Is <> 2002 Or 2003 Or 2004 Or 2005 Or 2006 Or 2007 _
Or 2008 Or 2009
MsgBox curcell.Address
Exit For
End Select
End Select
End Select
End Select
End Select

Next curcell

Next sht

 

[Dave Peterson]

First, I think you have a couple of problems:

Case Is = "String", "Date"
(in multiple locations.)

And second, I bet you want:

Select Case TypeName(curcell.Offset(-1, 0).Value)
Case Is = "String", "Date"

If you use:
Select Case TypeName(curcell.Offset(-1, 0))
without the .value, then typename() will always return Range.

 

[dbKemp]

I want to run several consective tests on "curcell". Select case seemed less
messy than nested if-then. However I am getting the above error on the **
line. Help appreciated

Dim curcell As Range
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets

For Each curcell In sht.UsedRange.Cells

Select Case curcell.Column
Case Is <> 1
Select Case curcell.Row
Case Is <> 1
Select Case TypeName(curcell.Offset(-1, 0))
**Case Is = "String" Or "Date"
Select Case TypeName(curcell.Offset(0, -1))
Case Is = "String" Or "Date"
Select Case curcell.Offset(-1, 0).Value
Case Is <> 2002 Or 2003 Or 2004 Or 2005 Or 2006 Or 2007 _
Or 2008 Or 2009
MsgBox curcell.Address
Exit For
End Select
End Select
End Select
End Select
End Select

Next curcell

Next sht

replace the 'or' with ','

 

[PBcorn]

Thanks. that worked. However i now find i have values 2004,2005,2006 etc
which are numbers, and not recognised as dates. so i tried case =
"string","date", 2003,2004,2005,2006 but this does not pick up these values.
please advise

 

[PBcorn]

sorry to confuse you. the original code was wrong, the <>2003,2004,2005..
should have been = 2003,2004,2005,2006...

 

[Dave Peterson]

Maybe you could use:

Case Is = "String", "Date", "Double"

ps. I find this easier to read:

Select Case curcell.Offset(-1, 0).Value
Case 2004 To 2009
'do nothing
Case Else
'do the real work
MsgBox curcell.Address
Exit For
End Select