Excel 2007 vba error

[Sabosis]

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

 

[Bernie Deitrick]

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]

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









- 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.

 

[Bernie Deitrick]

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

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









- 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.