Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 vba error
Hello-
I have a macro that works perfect in 2003, but 2007 is kicking up a runtime error '13' "type mismatch". Does anyone have an idea why this code will no longer work? Sub GetTotalA() Dim rngAllValues As Range Dim rngCurrent As Range Dim wks As Worksheet Dim dblTotal As Double Set wks = ActiveSheet With wks Set rngAllValues = .Range(.Range("H5"), _ .Cells(Rows.Count, "H").End(xlUp).Offset(-1, 0)) End With dblTotal = Application.Sum(rngAllValues) For Each rngCurrent In rngAllValues rngCurrent.Offset(0, 1).Value = rngCurrent.Value / dblTotal 'This is the line showing the error Next rngCurrent End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 vba error
My guess is that one cell (perhaps, H5?) has a string value.
Try it this way: Sub GetTotalA() Dim rngAllValues As Range Dim rngCurrent As Range Dim wks As Worksheet Dim dblTotal As Double Set wks = ActiveSheet With wks Set rngAllValues = .Range(.Range("H5"), _ .Cells(Rows.Count, "H").End(xlUp).Offset(-1, 0)) End With dblTotal = Application.Sum(rngAllValues) For Each rngCurrent In rngAllValues If IsNumeric(rngCurrent.Value) Then rngCurrent.Offset(0, 1).Value = rngCurrent.Value / dblTotal Else MsgBox "Cell " & rngCurrent.Address(False, False) _ & "'s value is not a number" End If Next rngCurrent End Sub HTH, Bernie MS Excel MVP "Sabosis" wrote in message ... Hello- I have a macro that works perfect in 2003, but 2007 is kicking up a runtime error '13' "type mismatch". Does anyone have an idea why this code will no longer work? Sub GetTotalA() Dim rngAllValues As Range Dim rngCurrent As Range Dim wks As Worksheet Dim dblTotal As Double Set wks = ActiveSheet With wks Set rngAllValues = .Range(.Range("H5"), _ .Cells(Rows.Count, "H").End(xlUp).Offset(-1, 0)) End With dblTotal = Application.Sum(rngAllValues) For Each rngCurrent In rngAllValues rngCurrent.Offset(0, 1).Value = rngCurrent.Value / dblTotal 'This is the line showing the error Next rngCurrent End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 vba error
On May 11, 5:23*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: My guess is that one cell (perhaps, H5?) has a string value. Try it this way: Sub GetTotalA() * * Dim rngAllValues As Range * * Dim rngCurrent As Range * * Dim wks As Worksheet * * Dim dblTotal As Double * * Set wks = ActiveSheet * * With wks * * * * Set rngAllValues = .Range(.Range("H5"), _ * * * * * * * * * * * * * * * * * .Cells(Rows.Count, "H").End(xlUp).Offset(-1, 0)) * * End With * * dblTotal = Application.Sum(rngAllValues) * * For Each rngCurrent In rngAllValues * * * * If IsNumeric(rngCurrent.Value) Then * * * * * * rngCurrent.Offset(0, 1).Value = rngCurrent.Value / dblTotal * * * * Else * * * * * * MsgBox "Cell " & rngCurrent.Address(False, False) _ * * * * * * * * *& "'s value is not a number" * * * * End If * * Next rngCurrent End Sub HTH, Bernie MS Excel MVP "Sabosis" wrote in message ... Hello- I have a macro that works perfect in 2003, but 2007 is kicking up a runtime error '13' "type mismatch". Does anyone have an idea why this code will no longer work? Sub GetTotalA() Dim rngAllValues As Range Dim rngCurrent As Range Dim wks As Worksheet Dim dblTotal As Double Set wks = ActiveSheet With wks Set rngAllValues = .Range(.Range("H5"), _ *.Cells(Rows.Count, "H").End(xlUp).Offset(-1, 0)) End With dblTotal = Application.Sum(rngAllValues) For Each rngCurrent In rngAllValues *rngCurrent.Offset(0, 1).Value = rngCurrent.Value / dblTotal *'This is the line showing the error Next rngCurrent End Sub- Hide quoted text - - Show quoted text - Bernie- The message box is popping up telling me that the values in H are not a number. The values in H are a 3 letter division code. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 vba error
Scott,
The message box is popping up telling me that the values in H are not a number. The values in H are a 3 letter division code. Could you post examples of your three letter codes and the sum that you would expect for the examples? Are you actually summing numbers associated with those values, perhaps in another column? Bernie MS Excel MVP "Sabosis" wrote in message ... On May 11, 5:23 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: My guess is that one cell (perhaps, H5?) has a string value. Try it this way: Sub GetTotalA() Dim rngAllValues As Range Dim rngCurrent As Range Dim wks As Worksheet Dim dblTotal As Double Set wks = ActiveSheet With wks Set rngAllValues = .Range(.Range("H5"), _ .Cells(Rows.Count, "H").End(xlUp).Offset(-1, 0)) End With dblTotal = Application.Sum(rngAllValues) For Each rngCurrent In rngAllValues If IsNumeric(rngCurrent.Value) Then rngCurrent.Offset(0, 1).Value = rngCurrent.Value / dblTotal Else MsgBox "Cell " & rngCurrent.Address(False, False) _ & "'s value is not a number" End If Next rngCurrent End Sub HTH, Bernie MS Excel MVP "Sabosis" wrote in message ... Hello- I have a macro that works perfect in 2003, but 2007 is kicking up a runtime error '13' "type mismatch". Does anyone have an idea why this code will no longer work? Sub GetTotalA() Dim rngAllValues As Range Dim rngCurrent As Range Dim wks As Worksheet Dim dblTotal As Double Set wks = ActiveSheet With wks Set rngAllValues = .Range(.Range("H5"), _ .Cells(Rows.Count, "H").End(xlUp).Offset(-1, 0)) End With dblTotal = Application.Sum(rngAllValues) For Each rngCurrent In rngAllValues rngCurrent.Offset(0, 1).Value = rngCurrent.Value / dblTotal 'This is the line showing the error Next rngCurrent End Sub- Hide quoted text - - Show quoted text - Bernie- The message box is popping up telling me that the values in H are not a number. The values in H are a 3 letter division code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 error | Excel Discussion (Misc queries) | |||
Excel 2007 - error saving file & error loading dll | Excel Discussion (Misc queries) | |||
X error bar Excel 2007 | Charts and Charting in Excel | |||
Error in Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 Error | Excel Programming |