|
|
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
|
|
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
Joanne wrote:
[Quoted Text] > 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 - Word MVP
My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org
|
|
You don't have to strip the "$" out of the summing equation:
vSum = vSum + CDbl(oNum)
Works also.
Joanne wrote:
[Quoted Text] > 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 - Word MVP
My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org
|
|
Thank you! You're brilliant.
"Greg Maxey" wrote:
[Quoted Text] > You don't have to strip the "$" out of the summing equation: > > vSum = vSum + CDbl(oNum) > > Works also. > > Joanne wrote: > > 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 - Word MVP > > My web site http://gregmaxey.mvps.org> Word MVP web site http://word.mvps.org> > > > |
|
|
[Quoted Text] > Thank you!
My pleasure.
>You're brilliant.
No. Just like a challenge and needed something to do.
Joanne wrote:
>
> "Greg Maxey" wrote:
>
>> You don't have to strip the "$" out of the summing equation:
>>
>> vSum = vSum + CDbl(oNum)
>>
>> Works also.
>>
>> Joanne wrote:
>>> 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 - Word MVP
>>
>> My web site http://gregmaxey.mvps.org
>> Word MVP web site http://word.mvps.org
--
Greg Maxey - Word MVP
My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org
|
|
Could this be adapted to perform on the whole column?
"Greg Maxey" wrote:
[Quoted Text] > > Thank you! > > My pleasure. > > >You're brilliant. > > No. Just like a challenge and needed something to do. > > > > Joanne wrote: > > > > "Greg Maxey" wrote: > > > >> You don't have to strip the "$" out of the summing equation: > >> > >> vSum = vSum + CDbl(oNum) > >> > >> Works also. > >> > >> Joanne wrote: > >>> 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 - Word MVP > >> > >> My web site http://gregmaxey.mvps.org> >> Word MVP web site http://word.mvps.org> > -- > Greg Maxey - Word MVP > > My web site http://gregmaxey.mvps.org> Word MVP web site http://word.mvps.org> > > > |
|
|