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

FOUND IT!!!

It was when I was taking a hard look at the formula in the Trigger
cell on Daily that I realized it was not using any named ranges!

So I revisited the three variables and rewrote them in the same manner
and it worked.

Here is the formula from the original trigger cell on Daily:
__________________________________________________ _______________

=IF(ISERROR(OFFSET(Input!$C$3,27*(DAY(FL2)-1)-1+CV41,2)),"",OFFSET(Input!$C$3,27*(DAY(FL2)-1)-1+CV41,2))
__________________________________________________ _______________

What this formula is doing is starting in the upper left hand corner
of the data area of sheet Input going to the day of interest by
multiplying the day (DAY(FL2)) by 27 and then going to the item in
that day by adding the item number (CV41) and taking the value from
the 2nd column to the right. In other words:

Reference = Input1!C3
Row = 27*(DAY(FL2)-1)-1+CV41
Column = 2

And here is the finale draft of the reformatting Conditional Formatter
for Daily:
__________________________________________________ __________________________

Public Sub rDailyColor()
'Formatting of "Daily" sheet
Dim iDay As Integer
Dim iDayItem As Integer
Dim rDailyItem As Range
Dim rInputPaidItem1 As Range
Dim rInputPaidItem2 As Range
Dim rDailyTrigger As Range
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Daily")
Set ws2 = wb1.Worksheets("Input")
On Error Resume Next
For iDay = 1 To 31
For iDayItem = 1 To 27
Set rDailyItem = _
Range("rDaily" & iDay & "_" & iDayItem)
Set rInputPaidItem1 = _
ws2.Range("C3").Offset(((iDay - 1) * 27) + iDayItem, 6)
Set rInputPaidItem2 = _
ws2.Range("C3").Offset(((iDay - 1) * 27) + iDayItem, 8)
Set rDailyTrigger = _
ws2.Range("C3").Offset(((iDay - 1) * 27) + iDayItem, 2)
Select Case rDailyTrigger
Case Is = vbNullString
With rDailyItem
.Interior.ColorIndex = xlNone
.Font.FontStyle = "Bold"
End With
Case Else
Select Case rInputPaidItem2
Case Is = vbNullString
Select Case rInputPaidItem1
Case Is = 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
__________________________________________________ __________________________

I don't know if I need the error canceling or not, but better safe
then sorry.

Thanks for sticking with me on this one, I tried to reply about 6
times in the last three days, every time I got about 4 or 6 pages
typed out , something in what I wrote would jump out at me and I would
try it out. This last time it was the lack of named ranges in the
formula that hit me. Tried it and it worked (had to tweak thee
columns a bit but that was the easy part)

Your suggestions were good. I tried them all and kept a few in the
finale draft.

And all of this happened because I forgot what those named ranges were
being used for and deleted them. I hadn't looked at that sheet in a
while because it was working. Imagine my shock to discover it wasn't
working anymore.

Anyway, I'm rambling.

Again, thanks. Your suggestions were very helpful and greatly
appreciated.

-Minitman


On Fri, 20 Jun 2008 07:17:12 -0500, Dave Peterson
wrote:

"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

Glad you got it working!

Minitman wrote:

<<snipped
--

Dave Peterson