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

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.



PBcorn wrote:

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

On Oct 29, 10:13 am, PBcorn wrote:
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 ','

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

"Dave Peterson" wrote:

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.



PBcorn wrote:

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

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

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

PBcorn wrote:

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

"Dave Peterson" wrote:

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.



PBcorn wrote:

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


--

Dave Peterson