Error 13

[Leslie]

Here is my code: I am getting an Error 13 - Type mismatch. Any ideas on why.
Thanks.

Sub CFormat()

Dim rng As Range, cell As Range
Dim ncol As Integer, lrow As Long
Dim pcnt As Double, divisor As Double

'Ace is sheet name
ThisWorkbook.Worksheets("Ace").Activate

' Find column for current Month (add 5 to start in colum F onwards)
ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"), 0) + 5

' Find last row of data in current month column
lrow = Cells(Rows.Count, ncol).End(xlUp).Row

' Set range to cells for current month starting row 9
Set rng = Range(Cells(20, ncol), Cells(lrow, ncol))

' Set Divisor for current month
divisor = Cells(5, ncol)

' Loop through all cells in range
For Each cell In rng
' Calculate percentage
pcnt = (cell / divisor) * 100
cell.Select
' Set colorindex based on percentage
Select Case pcnt
Case Is > 100
Selection.Interior.ColorIndex = 4
Case Is >= 90
Selection.Interior.ColorIndex = 35
Case Is >= 80
Selection.Interior.ColorIndex = 36
Case Is >= 70
Selection.Interior.ColorIndex = 7
Case Is >= 1
Selection.Interior.ColorIndex = 54
Case Else
Selection.Interior.ColorIndex = 3
End Select
Next cell

End Sub

[Chip Pearson]

What line of code is causing the problem?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Leslie" <Leslie@discussions.microsoft.com> wrote in message
news:0D2163FC-2D1F-4CB2-908A-D03C14D43CA3@microsoft.com...
> Here is my code: I am getting an Error 13 - Type mismatch. Any
> ideas on why.
> Thanks.
>
> Sub CFormat()
>
> Dim rng As Range, cell As Range
> Dim ncol As Integer, lrow As Long
> Dim pcnt As Double, divisor As Double
>
> 'Ace is sheet name
> ThisWorkbook.Worksheets("Ace").Activate
>
> ' Find column for current Month (add 5 to start in colum F
> onwards)
> ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"),
> 0) + 5
>
> ' Find last row of data in current month column
> lrow = Cells(Rows.Count, ncol).End(xlUp).Row
>
> ' Set range to cells for current month starting row 9
> Set rng = Range(Cells(20, ncol), Cells(lrow, ncol))
>
> ' Set Divisor for current month
> divisor = Cells(5, ncol)
>
> ' Loop through all cells in range
> For Each cell In rng
> ' Calculate percentage
> pcnt = (cell / divisor) * 100
> cell.Select
> ' Set colorindex based on percentage
> Select Case pcnt
> Case Is > 100
> Selection.Interior.ColorIndex = 4
> Case Is >= 90
> Selection.Interior.ColorIndex = 35
> Case Is >= 80
> Selection.Interior.ColorIndex = 36
> Case Is >= 70
> Selection.Interior.ColorIndex = 7
> Case Is >= 1
> Selection.Interior.ColorIndex = 54
> Case Else
> Selection.Interior.ColorIndex = 3
> End Select
> Next cell
>
> End Sub
>

[Leslie]

I can't tell. When I debug it highlights Sub and works its way down and hits
each line item. I'm very new at this is there something else I should be
doing to see which line item it stops at. Thanks.

"Chip Pearson" wrote:

> What line of code is causing the problem?
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Leslie" <Leslie@discussions.microsoft.com> wrote in message
> news:0D2163FC-2D1F-4CB2-908A-D03C14D43CA3@microsoft.com...
> > Here is my code: I am getting an Error 13 - Type mismatch. Any
> > ideas on why.
> > Thanks.
> >
> > Sub CFormat()
> >
> > Dim rng As Range, cell As Range
> > Dim ncol As Integer, lrow As Long
> > Dim pcnt As Double, divisor As Double
> >
> > 'Ace is sheet name
> > ThisWorkbook.Worksheets("Ace").Activate
> >
> > ' Find column for current Month (add 5 to start in colum F
> > onwards)
> > ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"),
> > 0) + 5
> >
> > ' Find last row of data in current month column
> > lrow = Cells(Rows.Count, ncol).End(xlUp).Row
> >
> > ' Set range to cells for current month starting row 9
> > Set rng = Range(Cells(20, ncol), Cells(lrow, ncol))
> >
> > ' Set Divisor for current month
> > divisor = Cells(5, ncol)
> >
> > ' Loop through all cells in range
> > For Each cell In rng
> > ' Calculate percentage
> > pcnt = (cell / divisor) * 100
> > cell.Select
> > ' Set colorindex based on percentage
> > Select Case pcnt
> > Case Is > 100
> > Selection.Interior.ColorIndex = 4
> > Case Is >= 90
> > Selection.Interior.ColorIndex = 35
> > Case Is >= 80
> > Selection.Interior.ColorIndex = 36
> > Case Is >= 70
> > Selection.Interior.ColorIndex = 7
> > Case Is >= 1
> > Selection.Interior.ColorIndex = 54
> > Case Else
> > Selection.Interior.ColorIndex = 3
> > End Select
> > Next cell
> >
> > End Sub
> >
>
>
>

[Tom Ogilvy]

You don't say where, but assume it is on the line that assigns a value to
ncol

You should dim ncol as variant

Dim ncol as Variant, lrow as long
. . .
ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"), 0)
If iserror(ncol) then
msgbox "Problems: " & Range(CurMonth").Value & " was not found"
Exit sub
else
ncol = ncol + 1
End if

--
Regards,
Tom Ogilvy

"Leslie" <Leslie@discussions.microsoft.com> wrote in message
news:0D2163FC-2D1F-4CB2-908A-D03C14D43CA3@microsoft.com...
> Here is my code: I am getting an Error 13 - Type mismatch. Any ideas on
why.
> Thanks.
>
> Sub CFormat()
>
> Dim rng As Range, cell As Range
> Dim ncol As Integer, lrow As Long
> Dim pcnt As Double, divisor As Double
>
> 'Ace is sheet name
> ThisWorkbook.Worksheets("Ace").Activate
>
> ' Find column for current Month (add 5 to start in colum F onwards)
> ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"), 0) + 5
>
> ' Find last row of data in current month column
> lrow = Cells(Rows.Count, ncol).End(xlUp).Row
>
> ' Set range to cells for current month starting row 9
> Set rng = Range(Cells(20, ncol), Cells(lrow, ncol))
>
> ' Set Divisor for current month
> divisor = Cells(5, ncol)
>
> ' Loop through all cells in range
> For Each cell In rng
> ' Calculate percentage
> pcnt = (cell / divisor) * 100
> cell.Select
> ' Set colorindex based on percentage
> Select Case pcnt
> Case Is > 100
> Selection.Interior.ColorIndex = 4
> Case Is >= 90
> Selection.Interior.ColorIndex = 35
> Case Is >= 80
> Selection.Interior.ColorIndex = 36
> Case Is >= 70
> Selection.Interior.ColorIndex = 7
> Case Is >= 1
> Selection.Interior.ColorIndex = 54
> Case Else
> Selection.Interior.ColorIndex = 3
> End Select
> Next cell
>
> End Sub
>

[Leslie]

I noticed it stops working when it hits a #DIV/0 on the spreadsheet. Any
ideas. Thanks.

"Tom Ogilvy" wrote:

> You don't say where, but assume it is on the line that assigns a value to
> ncol
>
> You should dim ncol as variant
>
> Dim ncol as Variant, lrow as long
> . . .
> ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"), 0)
> If iserror(ncol) then
> msgbox "Problems: " & Range(CurMonth").Value & " was not found"
> Exit sub
> else
> ncol = ncol + 1
> End if
>
> --
> Regards,
> Tom Ogilvy
>
> "Leslie" <Leslie@discussions.microsoft.com> wrote in message
> news:0D2163FC-2D1F-4CB2-908A-D03C14D43CA3@microsoft.com...
> > Here is my code: I am getting an Error 13 - Type mismatch. Any ideas on
> why.
> > Thanks.
> >
> > Sub CFormat()
> >
> > Dim rng As Range, cell As Range
> > Dim ncol As Integer, lrow As Long
> > Dim pcnt As Double, divisor As Double
> >
> > 'Ace is sheet name
> > ThisWorkbook.Worksheets("Ace").Activate
> >
> > ' Find column for current Month (add 5 to start in colum F onwards)
> > ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"), 0) + 5
> >
> > ' Find last row of data in current month column
> > lrow = Cells(Rows.Count, ncol).End(xlUp).Row
> >
> > ' Set range to cells for current month starting row 9
> > Set rng = Range(Cells(20, ncol), Cells(lrow, ncol))
> >
> > ' Set Divisor for current month
> > divisor = Cells(5, ncol)
> >
> > ' Loop through all cells in range
> > For Each cell In rng
> > ' Calculate percentage
> > pcnt = (cell / divisor) * 100
> > cell.Select
> > ' Set colorindex based on percentage
> > Select Case pcnt
> > Case Is > 100
> > Selection.Interior.ColorIndex = 4
> > Case Is >= 90
> > Selection.Interior.ColorIndex = 35
> > Case Is >= 80
> > Selection.Interior.ColorIndex = 36
> > Case Is >= 70
> > Selection.Interior.ColorIndex = 7
> > Case Is >= 1
> > Selection.Interior.ColorIndex = 54
> > Case Else
> > Selection.Interior.ColorIndex = 3
> > End Select
> > Next cell
> >
> > End Sub
> >
>
>
>

[Tom Ogilvy]

Sub CFormat()

Dim rng As Range, cell As Range
Dim ncol As Variant, lrow As Long
Dim pcnt As Double, divisor As Double

'Ace is sheet name
ThisWorkbook.Worksheets("Ace").Activate

' Find column for current Month (add 5 to start in colum F onwards)
ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"), 0) + 5
if iserror(ncol) then
msgbox "Problems: " & Range(CurMonth").Value & " was not found"
exit sub
Else
ncol = ncol + 5
End If

' Find last row of data in current month column
lrow = Cells(Rows.Count, ncol).End(xlUp).Row

' Set range to cells for current month starting row 9
Set rng = Range(Cells(20, ncol), Cells(lrow, ncol))

' Set Divisor for current month
divisor = Cells(5, ncol)
if divisor = 0 then
msgbox "Divisor is zero"
exit sub
End if
' Loop through all cells in range
For Each cell In rng
' Calculate percentage
if isnumeric(cell) then
pcnt = (cell / divisor) * 100
cell.Select
' Set colorindex based on percentage
Select Case pcnt
Case Is > 100
Selection.Interior.ColorIndex = 4
Case Is >= 90
Selection.Interior.ColorIndex = 35
Case Is >= 80
Selection.Interior.ColorIndex = 36
Case Is >= 70
Selection.Interior.ColorIndex = 7
Case Is >= 1
Selection.Interior.ColorIndex = 54
Case Else
Selection.Interior.ColorIndex = 3
End Select
End If
Next cell

End Sub

--
Regards,
Tom Ogilvy


"Leslie" <Leslie@discussions.microsoft.com> wrote in message
news:22DC9ADE-9CE3-421B-9157-3CE66CB1469F@microsoft.com...
> I noticed it stops working when it hits a #DIV/0 on the spreadsheet. Any
> ideas. Thanks.
>
> "Tom Ogilvy" wrote:
>
> > You don't say where, but assume it is on the line that assigns a value
to
> > ncol
> >
> > You should dim ncol as variant
> >
> > Dim ncol as Variant, lrow as long
> > . . .
> > ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"), 0)
> > If iserror(ncol) then
> > msgbox "Problems: " & Range(CurMonth").Value & " was not found"
> > Exit sub
> > else
> > ncol = ncol + 1
> > End if
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Leslie" <Leslie@discussions.microsoft.com> wrote in message
> > news:0D2163FC-2D1F-4CB2-908A-D03C14D43CA3@microsoft.com...
> > > Here is my code: I am getting an Error 13 - Type mismatch. Any ideas
on
> > why.
> > > Thanks.
> > >
> > > Sub CFormat()
> > >
> > > Dim rng As Range, cell As Range
> > > Dim ncol As Integer, lrow As Long
> > > Dim pcnt As Double, divisor As Double
> > >
> > > 'Ace is sheet name
> > > ThisWorkbook.Worksheets("Ace").Activate
> > >
> > > ' Find column for current Month (add 5 to start in colum F onwards)
> > > ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"), 0) + 5
> > >
> > > ' Find last row of data in current month column
> > > lrow = Cells(Rows.Count, ncol).End(xlUp).Row
> > >
> > > ' Set range to cells for current month starting row 9
> > > Set rng = Range(Cells(20, ncol), Cells(lrow, ncol))
> > >
> > > ' Set Divisor for current month
> > > divisor = Cells(5, ncol)
> > >
> > > ' Loop through all cells in range
> > > For Each cell In rng
> > > ' Calculate percentage
> > > pcnt = (cell / divisor) * 100
> > > cell.Select
> > > ' Set colorindex based on percentage
> > > Select Case pcnt
> > > Case Is > 100
> > > Selection.Interior.ColorIndex = 4
> > > Case Is >= 90
> > > Selection.Interior.ColorIndex = 35
> > > Case Is >= 80
> > > Selection.Interior.ColorIndex = 36
> > > Case Is >= 70
> > > Selection.Interior.ColorIndex = 7
> > > Case Is >= 1
> > > Selection.Interior.ColorIndex = 54
> > > Case Else
> > > Selection.Interior.ColorIndex = 3
> > > End Select
> > > Next cell
> > >
> > > End Sub
> > >
> >
> >
> >

[Leslie]

Thanks very much its working now.

"Tom Ogilvy" wrote:

> Sub CFormat()
>
> Dim rng As Range, cell As Range
> Dim ncol As Variant, lrow As Long
> Dim pcnt As Double, divisor As Double
>
> 'Ace is sheet name
> ThisWorkbook.Worksheets("Ace").Activate
>
> ' Find column for current Month (add 5 to start in colum F onwards)
> ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"), 0) + 5
> if iserror(ncol) then
> msgbox "Problems: " & Range(CurMonth").Value & " was not found"
> exit sub
> Else
> ncol = ncol + 5
> End If
>
> ' Find last row of data in current month column
> lrow = Cells(Rows.Count, ncol).End(xlUp).Row
>
> ' Set range to cells for current month starting row 9
> Set rng = Range(Cells(20, ncol), Cells(lrow, ncol))
>
> ' Set Divisor for current month
> divisor = Cells(5, ncol)
> if divisor = 0 then
> msgbox "Divisor is zero"
> exit sub
> End if
> ' Loop through all cells in range
> For Each cell In rng
> ' Calculate percentage
> if isnumeric(cell) then
> pcnt = (cell / divisor) * 100
> cell.Select
> ' Set colorindex based on percentage
> Select Case pcnt
> Case Is > 100
> Selection.Interior.ColorIndex = 4
> Case Is >= 90
> Selection.Interior.ColorIndex = 35
> Case Is >= 80
> Selection.Interior.ColorIndex = 36
> Case Is >= 70
> Selection.Interior.ColorIndex = 7
> Case Is >= 1
> Selection.Interior.ColorIndex = 54
> Case Else
> Selection.Interior.ColorIndex = 3
> End Select
> End If
> Next cell
>
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Leslie" <Leslie@discussions.microsoft.com> wrote in message
> news:22DC9ADE-9CE3-421B-9157-3CE66CB1469F@microsoft.com...
> > I noticed it stops working when it hits a #DIV/0 on the spreadsheet. Any
> > ideas. Thanks.
> >
> > "Tom Ogilvy" wrote:
> >
> > > You don't say where, but assume it is on the line that assigns a value
> to
> > > ncol
> > >
> > > You should dim ncol as variant
> > >
> > > Dim ncol as Variant, lrow as long
> > > . . .
> > > ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"), 0)
> > > If iserror(ncol) then
> > > msgbox "Problems: " & Range(CurMonth").Value & " was not found"
> > > Exit sub
> > > else
> > > ncol = ncol + 1
> > > End if
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Leslie" <Leslie@discussions.microsoft.com> wrote in message
> > > news:0D2163FC-2D1F-4CB2-908A-D03C14D43CA3@microsoft.com...
> > > > Here is my code: I am getting an Error 13 - Type mismatch. Any ideas
> on
> > > why.
> > > > Thanks.
> > > >
> > > > Sub CFormat()
> > > >
> > > > Dim rng As Range, cell As Range
> > > > Dim ncol As Integer, lrow As Long
> > > > Dim pcnt As Double, divisor As Double
> > > >
> > > > 'Ace is sheet name
> > > > ThisWorkbook.Worksheets("Ace").Activate
> > > >
> > > > ' Find column for current Month (add 5 to start in colum F onwards)
> > > > ncol = Application.Match(Range("CurMonth"), Range("HdrMonths"), 0) + 5
> > > >
> > > > ' Find last row of data in current month column
> > > > lrow = Cells(Rows.Count, ncol).End(xlUp).Row
> > > >
> > > > ' Set range to cells for current month starting row 9
> > > > Set rng = Range(Cells(20, ncol), Cells(lrow, ncol))
> > > >
> > > > ' Set Divisor for current month
> > > > divisor = Cells(5, ncol)
> > > >
> > > > ' Loop through all cells in range
> > > > For Each cell In rng
> > > > ' Calculate percentage
> > > > pcnt = (cell / divisor) * 100
> > > > cell.Select
> > > > ' Set colorindex based on percentage
> > > > Select Case pcnt
> > > > Case Is > 100
> > > > Selection.Interior.ColorIndex = 4
> > > > Case Is >= 90
> > > > Selection.Interior.ColorIndex = 35
> > > > Case Is >= 80
> > > > Selection.Interior.ColorIndex = 36
> > > > Case Is >= 70
> > > > Selection.Interior.ColorIndex = 7
> > > > Case Is >= 1
> > > > Selection.Interior.ColorIndex = 54
> > > > Case Else
> > > > Selection.Interior.ColorIndex = 3
> > > > End Select
> > > > Next cell
> > > >
> > > > End Sub
> > > >
> > >
> > >
> > >
>
>
>