Greetings,

I am getting a type mismatch error on this code:
__________________________________________________ ___________________________
Public Sub rDailyColor()
'Formatting of "Daily" sheet
Dim rDailyItem As Range
Dim rInputPaidItem1 As Range
Dim rInputPaidItem2 As Range
Dim rInputTrigger As Range

' On Error Resume Next ' Had to rem this out to get the error
For iDay = 1 To 31
For iDayItem = 1 To 27
Set rDailyItem = _
Range("rDaily" & iDay & "_" & iDayItem)
Set rInputPaidItem1 = _
Range("rInput" & iDay).Offset(iDayItem, _
7)
Set rInputPaidItem2 = _
Range("rInput" & iDay).Offset(iDayItem, _
9)
Set rInputTrigger = _
Range("rInput" & iDay).Offset(iDayItem, _
34)
Select Case rInputTrigger
Case vbNullString '<<<< This is the line
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
Case Else
Select Case rInputPaidItem2
Case vbNullString
Select Case rInputPaidItem1
Case vbNullString
With rDailyItem
.Interior.ColorIndex = 38 '38
.Font.FontStyle = "Bold"
End With
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
End Select
Next iDayItem
Next iDay
On Error GoTo 0
End Sub
__________________________________________________ ___________________________
Any ideas as to what needs to be done to correct this problem?

Any suggestions are welcome and appreciated.

-Minitman

With no testing at all, I'd try:

Case "" '<<<< This is the line



Minitman wrote:

Greetings,

I am getting a type mismatch error on this code:
__________________________________________________ ___________________________
Public Sub rDailyColor()
'Formatting of "Daily" sheet
Dim rDailyItem As Range
Dim rInputPaidItem1 As Range
Dim rInputPaidItem2 As Range
Dim rInputTrigger As Range

' On Error Resume Next ' Had to rem this out to get the error
For iDay = 1 To 31
For iDayItem = 1 To 27
Set rDailyItem = _
Range("rDaily" & iDay & "_" & iDayItem)
Set rInputPaidItem1 = _
Range("rInput" & iDay).Offset(iDayItem, _
7)
Set rInputPaidItem2 = _
Range("rInput" & iDay).Offset(iDayItem, _
9)
Set rInputTrigger = _
Range("rInput" & iDay).Offset(iDayItem, _
34)
Select Case rInputTrigger
Case vbNullString '<<<< This is the line
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
Case Else
Select Case rInputPaidItem2
Case vbNullString
Select Case rInputPaidItem1
Case vbNullString
With rDailyItem
.Interior.ColorIndex = 38 '38
.Font.FontStyle = "Bold"
End With
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
End Select
Next iDayItem
Next iDay
On Error GoTo 0
End Sub
__________________________________________________ ___________________________
Any ideas as to what needs to be done to correct this problem?

Any suggestions are welcome and appreciated.

-Minitman

--

Dave Peterson

Hey Dave,

Thanks for the reply.

Unfortunately, it had the same effect as "", vbNullString and Nothing
( the other empty cell names I could think of) which is to say it
still errors out.

Any other ideas?

-Minitman



On Thu, 19 Jun 2008 17:32:16 -0500, Dave Peterson
wrote:

With no testing at all, I'd try:

Case "" '<<<< This is the line



Minitman wrote:

Greetings,

I am getting a type mismatch error on this code:
__________________________________________________ ___________________________
Public Sub rDailyColor()
'Formatting of "Daily" sheet
Dim rDailyItem As Range
Dim rInputPaidItem1 As Range
Dim rInputPaidItem2 As Range
Dim rInputTrigger As Range

' On Error Resume Next ' Had to rem this out to get the error
For iDay = 1 To 31
For iDayItem = 1 To 27
Set rDailyItem = _
Range("rDaily" & iDay & "_" & iDayItem)
Set rInputPaidItem1 = _
Range("rInput" & iDay).Offset(iDayItem, _
7)
Set rInputPaidItem2 = _
Range("rInput" & iDay).Offset(iDayItem, _
9)
Set rInputTrigger = _
Range("rInput" & iDay).Offset(iDayItem, _
34)
Select Case rInputTrigger
Case vbNullString '<<<< This is the line
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
Case Else
Select Case rInputPaidItem2
Case vbNullString
Select Case rInputPaidItem1
Case vbNullString
With rDailyItem
.Interior.ColorIndex = 38 '38
.Font.FontStyle = "Bold"
End With
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
End Select
Next iDayItem
Next iDay
On Error GoTo 0
End Sub
__________________________________________________ ___________________________
Any ideas as to what needs to be done to correct this problem?

Any suggestions are welcome and appreciated.

-Minitman

I've changed the code slightly, but have run out of ideas as to what
to try next
__________________________________
Public Sub rDailyColor()
'Formatting of "Daily" sheet
Dim rDailyItem As Range
Dim rInputPaidItem1 As Range
Dim rInputPaidItem2 As Range
Dim rInputTrigger As Range
' On Error Resume Next
For iDay = 1 To 31
For iDayItem = 1 To 27
Set rDailyItem = _
Range("rDaily" & iDay & "_" & iDayItem)
Set rInputPaidItem1 = _
Range("rInput" & iDay).Offset(iDayItem, _
7)
Set rInputPaidItem2 = _
Range("rInput" & iDay).Offset(iDayItem, _
9)
Set rInputTrigger = _
Range("rInput" & iDay).Offset(iDayItem, _
34)
Select Case rInputTrigger
Case """"
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
Case Else
Select Case rInputPaidItem2
Case """"
Select Case rInputPaidItem1
Case """"
With rDailyItem
.Interior.ColorIndex = 38 '38
.Font.FontStyle = "Bold"
End With
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
End Select
Next iDayItem
Next iDay
On Error GoTo 0

End Sub
__________________________________________________ ___________________________

Any ideas as to what needs to be done to correct this problem?

Any suggestions are welcome and appreciated.

-Minitman

You can get that mismatch error if the range contains an error.

so maybe...

if iserror(rInputTrigger.value) then
'what should happen
else
select case rInputTrigger.value
case vbnullstring 'should work

And you can get that error if the range is more than one cell. But that's
difficult to check in your code.

'maybe...
Select Case rInputTrigger.cells(1).value

or
if rInputTrigger.cells.count 1 then
'what should happen???
else
if iserror(rInputTrigger.value) then
'what should happen
else
select case rInputTrigger.value
case is = vbnullstring 'I like that syntax, but it doesn't matter.

.....


Minitman wrote:

I've changed the code slightly, but have run out of ideas as to what
to try next
__________________________________
Public Sub rDailyColor()
'Formatting of "Daily" sheet
Dim rDailyItem As Range
Dim rInputPaidItem1 As Range
Dim rInputPaidItem2 As Range
Dim rInputTrigger As Range
' On Error Resume Next
For iDay = 1 To 31
For iDayItem = 1 To 27
Set rDailyItem = _
Range("rDaily" & iDay & "_" & iDayItem)
Set rInputPaidItem1 = _
Range("rInput" & iDay).Offset(iDayItem, _
7)
Set rInputPaidItem2 = _
Range("rInput" & iDay).Offset(iDayItem, _
9)
Set rInputTrigger = _
Range("rInput" & iDay).Offset(iDayItem, _
34)
Select Case rInputTrigger
Case """"
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
Case Else
Select Case rInputPaidItem2
Case """"
Select Case rInputPaidItem1
Case """"
With rDailyItem
.Interior.ColorIndex = 38 '38
.Font.FontStyle = "Bold"
End With
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
End Select
Next iDayItem
Next iDay
On Error GoTo 0

End Sub
__________________________________________________ ___________________________

Any ideas as to what needs to be done to correct this problem?

Any suggestions are welcome and appreciated.

-Minitman

--

Dave Peterson

"But that's difficult to check in your code."

I didn't mean that it was difficult to check for multiple cells in code. I
meant that it was difficult to check if your specific range name refered to
multiple cells.

Dave Peterson wrote:

You can get that mismatch error if the range contains an error.

so maybe...

if iserror(rInputTrigger.value) then
'what should happen
else
select case rInputTrigger.value
case vbnullstring 'should work

And you can get that error if the range is more than one cell. But that's
difficult to check in your code.

'maybe...
Select Case rInputTrigger.cells(1).value

or
if rInputTrigger.cells.count 1 then
'what should happen???
else
if iserror(rInputTrigger.value) then
'what should happen
else
select case rInputTrigger.value
case is = vbnullstring 'I like that syntax, but it doesn't matter.

.....

Minitman wrote:

I've changed the code slightly, but have run out of ideas as to what
to try next
__________________________________
Public Sub rDailyColor()
'Formatting of "Daily" sheet
Dim rDailyItem As Range
Dim rInputPaidItem1 As Range
Dim rInputPaidItem2 As Range
Dim rInputTrigger As Range
' On Error Resume Next
For iDay = 1 To 31
For iDayItem = 1 To 27
Set rDailyItem = _
Range("rDaily" & iDay & "_" & iDayItem)
Set rInputPaidItem1 = _
Range("rInput" & iDay).Offset(iDayItem, _
7)
Set rInputPaidItem2 = _
Range("rInput" & iDay).Offset(iDayItem, _
9)
Set rInputTrigger = _
Range("rInput" & iDay).Offset(iDayItem, _
34)
Select Case rInputTrigger
Case """"
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
Case Else
Select Case rInputPaidItem2
Case """"
Select Case rInputPaidItem1
Case """"
With rDailyItem
.Interior.ColorIndex = 38 '38
.Font.FontStyle = "Bold"
End With
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
Case Else
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
End Select
End Select
Next iDayItem
Next iDay
On Error GoTo 0

End Sub
__________________________________________________ ___________________________

Any ideas as to what needs to be done to correct this problem?

Any suggestions are welcome and appreciated.

-Minitman

--

Dave Peterson

--

Dave Peterson