Type mismatch again

[Joanne]

Hello,
I feel so frustrated with myself in not understanding values within cells
vs. the cells themselves. I am trying to total a column in a table in Word
2003. Some of the cells may be empty. The total will be put in cell E19 (at
least in this case). When some people tested the macro, we found that it is
not totaling the cells if the user placed a $ in front of the numeric value,
so I thought I should make sure that the cell's values are seen as numeric.
Here's what I did. I tried the format with both .range just alone and also
..range.text and it still gives a type mismatch.
Any help would be greatly, greatly appreciated.


Sub ConvertToCurrencyAndAdvance()
Dim i As Long, j As Long, vSum As Long
Dim oNum As Range
vSum = 0

If Not Selection.Information(wdWithInTable) Then
MsgBox "Please place the cursor inside the table & restart macro"
Exit Sub
End If
i = ActiveDocument.Tables(1).Rows.Count

For j = 9 To i

With Selection.Tables(1)
Set oNum = .Cell(i, 5).Range
oNum.End = oNum.End - 1
MsgBox oNum
.Cell(i, 5).Range.Text = FormatCurrency(Expression:=oNum, _
NumDigitsAfterDecimal:=2, IncludeLeadingDigit:=vbTrue, _
UseParensForNegativeNumbers:=vbTrue)
End With

'vSum = vSum + Val(ActiveDocument.Tables(1).Cell(Row:=i,
Column:=5).Range.Text)
MsgBox vSum
Next j
'
'myTable.Cell(i, 5).Range.InsertAfter (vSum)
End Sub

 

[Greg Maxey]

Joanne,

You are learning that cell ranges are tricky ;-)

Sub ConvertToCurrencyAndAdvance()
Dim i As Long, j As Long, vSum As Double
Dim oNum As Range
Dim myTable As Word.Table
vSum = 0
If Not Selection.Information(wdWithInTable) Then
MsgBox "Please place the cursor inside the table & restart macro"
Exit Sub
Else
Set myTable = Selection.Tables(1)
End If
i = myTable.Rows.Count
For j = 9 To i - 1
With myTable
'Get the value of the cell
Set oNum = .Cell(j, 5).Range
'Strip end of cell marker
oNum.End = oNum.End - 1
'If cell value is numeric then format
If IsNumeric(oNum) Then
.Cell(j, 5).Range.Text = FormatCurrency(Expression:=oNum, _
NumDigitsAfterDecimal:=2, IncludeLeadingDigit:=vbTrue, _
UseParensForNegativeNumbers:=vbTrue)
'Get the new cell value
Set oNum = .Cell(j, 5).Range
'Strip the end of cell marker
oNum.End = oNum.End - 1
'Add it up
vSum = vSum + CDbl(Mid(oNum, 2, Len(oNum) - 1))
End If
End With
Next j
myTable.Cell(i, 5).Range.Text = FormatCurrency(Expression:=vSum, _
NumDigitsAfterDecimal:=2, IncludeLeadingDigit:=vbTrue, _
UseParensForNegativeNumbers:=vbTrue)
End Sub

 

[Greg Maxey]

You don't have to strip the "$" out of the summing equation:

vSum = vSum + CDbl(oNum)

Works also.

 

[Joanne]

Thank you! You're brilliant.

You don't have to strip the "$" out of the summing equation:

vSum = vSum + CDbl(oNum)

Works also.

 

[Greg Maxey]

Thank you!

My pleasure.

You're brilliant.

No. Just like a challenge and needed something to do.

 

[Dorak]

Could this be adapted to perform on the whole column?