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

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

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.

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.