| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 13
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 13
What line of code is causing the problem?
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Leslie" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 13
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 13
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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 13
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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 13
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" wrote in message ... 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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error 13
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" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error: "Excel encountered an error and had to remove some formatti | Excel Discussion (Misc queries) | |||
| Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
| Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
| Automation Error, Unknown Error. Error value - 440 | Excel Programming | |||
Linear Mode
