I know it's heresy to branch. So how do I handle this situation? I have
some code taking a date from a cell and doing a MONTH command to
extract the month and process based on that month number. Sometimes,
the cell that normally contains the date contains text like "na" or
"?". Then I get a type mismatch error on this line:

dteColCode = rngColCode.Value

How can I instruct it to skip down to the line "Next rCell" if an error
occurs on this line?
Thanks for your help.

Shoule have mentioned that dteColCode is dimmed as date, rngColCode is
dimmed as range...

Option Explicit
Sub testme()

Dim dteColCode As Variant
Dim rngColCode As Range

Set rngColCode = ActiveSheet.Range("a1")

dteColCode = rngColCode.Value
If IsDate(dteColCode) Then
'do your stuff
MsgBox "it looks like a date"
Else
'skip it
MsgBox "not a date"
End If

End Sub

davegb wrote:

I know it's heresy to branch. So how do I handle this situation? I have
some code taking a date from a cell and doing a MONTH command to
extract the month and process based on that month number. Sometimes,
the cell that normally contains the date contains text like "na" or
"?". Then I get a type mismatch error on this line:

dteColCode = rngColCode.Value

How can I instruct it to skip down to the line "Next rCell" if an error
occurs on this line?
Thanks for your help.

--

Dave Peterson

Dave Peterson wrote:
Option Explicit
Sub testme()

Dim dteColCode As Variant
Dim rngColCode As Range

Set rngColCode = ActiveSheet.Range("a1")

dteColCode = rngColCode.Value
If IsDate(dteColCode) Then
'do your stuff
MsgBox "it looks like a date"
Else
'skip it
MsgBox "not a date"
End If

End Sub

davegb wrote:

I know it's heresy to branch. So how do I handle this situation? I have
some code taking a date from a cell and doing a MONTH command to
extract the month and process based on that month number. Sometimes,
the cell that normally contains the date contains text like "na" or
"?". Then I get a type mismatch error on this line:

dteColCode = rngColCode.Value

How can I instruct it to skip down to the line "Next rCell" if an error
occurs on this line?
Thanks for your help.

--

Dave Peterson

Dave, thanks again. I changed the macro, but I'm still having a problem
with the cell that normally has a date in it is blank.

Sub CountMonth()

Dim lngRsnCode As Long
Dim wksSrc As Worksheet
Dim wksMon As Worksheet
Dim wksTot As Worksheet
Dim rngCode As Range
Dim lEndRow As Long
Dim strMonWksht As String
Dim dteColCode As Date

Dim lngCntctMo As Long
Dim lngMoRow As Long
Dim rngCell As Range
Dim varColCode As Variant
Dim strColCode As String


Const PWORD As String = "2005totals"
lEndRow = 1000

Set wksSrc = ActiveSheet
Set wksTot = ActiveWorkbook.Sheets("TOTALS")
Set rngCode = wksSrc.Range("D8:D" & lEndRow)
wksTot.Unprotect Password:=PWORD

strMonWksht = wksSrc.Name & " - Monthly"
Set wksMon = Sheets(strMonWksht)

wksMon.Range("B4:K15").ClearContents

For Each rngCell In rngCode
rngCell.Select

If rngCell < "na" Then
If rngCell < "?" Then
If Len(rngCell) < 3 Then
If rngCell < 0 Then
If rngCell < 11 Then
If rngCell < 15 Then
If rngCell < "" Then
Set varColCode = rngCell.Offset(0, 5)
If varColCode.Value < "" Then <-- not doing what I want it to
do
dteColCode = varColCode.Value<--- TYPE MISMATCH
If IsDate(dteColCode) Then
lngCntctMo = Month(dteColCode)
lngMoRow = lngCntctMo + 3
lngRsnCode = rngCell
wksTot.Range("AC1") = lngRsnCode
strColCode = wksTot.Range("AC2")
wksMon.Cells(lngMoRow, strColCode) = _
wksMon.Cells(lngMoRow, strColCode) + 1
End If
End If
End If
End If
End If
End If
End If
End If
End If

Next rngCell

wksTot.Protect Password:=PWORD
wksTot.Select

End Sub

The line above the error (marked "not doing what I want it to do") is
supposed to check for a blank cell, and bypass the rest of the
instructions if it is and go to "Next rngCell". I think this is what is
giving me the type mismatch on the next line. Any suggestions?

For Each rngCell In rngCode
rngCell.Select

If rngCell < "na" Then
If rngCell < "?" Then
If Len(rngCell) < 3 Then
If rngCell < 0 Then
If rngCell < 11 Then
If rngCell < 15 Then
If rngCell < "" Then
Set varColCode = rngCell.Offset(0, 5)
If Trim(varColCode.Text) < "" Then
dteColCode = varColCode.Value
If IsDate(dteColCode) Then
lngCntctMo = Month(dteColCode)
lngMoRow = lngCntctMo + 3
lngRsnCode = rngCell
wksTot.Range("AC1") = lngRsnCode
strColCode = wksTot.Range("AC2")
wksMon.Cells(lngMoRow, strColCode) = _
wksMon.Cells(lngMoRow, strColCode) + 1
End If
End If
End If
End If
End If
End If
End If
End If
End If

--
Regards,
Tom Ogilvy

"davegb" wrote in message
oups.com...

Dave Peterson wrote:
Option Explicit
Sub testme()

Dim dteColCode As Variant
Dim rngColCode As Range

Set rngColCode = ActiveSheet.Range("a1")

dteColCode = rngColCode.Value
If IsDate(dteColCode) Then
'do your stuff
MsgBox "it looks like a date"
Else
'skip it
MsgBox "not a date"
End If

End Sub

davegb wrote:

I know it's heresy to branch. So how do I handle this situation? I
have
some code taking a date from a cell and doing a MONTH command to
extract the month and process based on that month number. Sometimes,
the cell that normally contains the date contains text like "na" or
"?". Then I get a type mismatch error on this line:

dteColCode = rngColCode.Value

How can I instruct it to skip down to the line "Next rCell" if an
error
occurs on this line?
Thanks for your help.

--

Dave Peterson

Dave, thanks again. I changed the macro, but I'm still having a problem
with the cell that normally has a date in it is blank.

Sub CountMonth()

Dim lngRsnCode As Long
Dim wksSrc As Worksheet
Dim wksMon As Worksheet
Dim wksTot As Worksheet
Dim rngCode As Range
Dim lEndRow As Long
Dim strMonWksht As String
Dim dteColCode As Date

Dim lngCntctMo As Long
Dim lngMoRow As Long
Dim rngCell As Range
Dim varColCode As Variant
Dim strColCode As String


Const PWORD As String = "2005totals"
lEndRow = 1000

Set wksSrc = ActiveSheet
Set wksTot = ActiveWorkbook.Sheets("TOTALS")
Set rngCode = wksSrc.Range("D8:D" & lEndRow)
wksTot.Unprotect Password:=PWORD

strMonWksht = wksSrc.Name & " - Monthly"
Set wksMon = Sheets(strMonWksht)

wksMon.Range("B4:K15").ClearContents

For Each rngCell In rngCode
rngCell.Select

If rngCell < "na" Then
If rngCell < "?" Then
If Len(rngCell) < 3 Then
If rngCell < 0 Then
If rngCell < 11 Then
If rngCell < 15 Then
If rngCell < "" Then
Set varColCode = rngCell.Offset(0, 5)
If varColCode.Value < "" Then <-- not doing what I want it to
do
dteColCode = varColCode.Value<--- TYPE MISMATCH
If IsDate(dteColCode) Then
lngCntctMo = Month(dteColCode)
lngMoRow = lngCntctMo + 3
lngRsnCode = rngCell
wksTot.Range("AC1") = lngRsnCode
strColCode = wksTot.Range("AC2")
wksMon.Cells(lngMoRow, strColCode) = _
wksMon.Cells(lngMoRow, strColCode) + 1
End If
End If
End If
End If
End If
End If
End If
End If
End If

Next rngCell

wksTot.Protect Password:=PWORD
wksTot.Select

End Sub

The line above the error (marked "not doing what I want it to do") is
supposed to check for a blank cell, and bypass the rest of the
instructions if it is and go to "Next rngCell". I think this is what is
giving me the type mismatch on the next line. Any suggestions?

Thanks again, Tom!