If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to format negative numbers in Word tables
I can see how to custom format the sum of a column of numbers but not how to
format the raw numbers entered into the individual cells above. Be great if someone could put me on the right track please. Many thanks |
#2
|
|||
|
|||
How to format negative numbers in Word tables
Warrain,
If you are using a simple Word document with a table then you can't enter raw numbers and have the come out formatted in real time. You can type your numbers as raw then select the appropriate cells and format all selected cells using a macro. Here is an example: Sub ConvertSelectedRawNumbersInTableToCurrencyFormat() If Not Selection.Information(wdWithInTable) Then MsgBox "Place cursor in a table cell or select multiple cells." Exit Sub End If Dim oCl As Word.Cell Dim oRng As Range Dim Count As Integer For Each oCl In Selection.Cells Set oRng = oCl.Range oRng.End = oRng.End - 1 With oRng If IsNumeric(oRng) Then .Text = FormatCurrency _ (Expression:=.Text, _ NumDigitsAfterDecimal:=2, _ IncludeLeadingDigit:=vbTrue, _ UseParensForNegativeNumbers:=vbTrue) End If ' If InStr(oRng.Text, "$") = False Then If IsNumeric(oRng) = False Then Count = Count + 1 End If End With Next oCl If Count = 0 Then MsgBox "Conversion complete." End If Selection.Collapse wdCollapseEnd If Count = 1 Then MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" End If If Count 1 Then MsgBox "" & Count & " of the selected cells are empty or content is not numerical. Conversion complete on all selected numerical cells.", , "Notice!!" End If End Sub -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Warrain wrote: I can see how to custom format the sum of a column of numbers but not how to format the raw numbers entered into the individual cells above. Be great if someone could put me on the right track please. Many thanks |
#3
|
|||
|
|||
How to format negative numbers in Word tables
Wow! Thanks Greg for the prompt and very informative response.
"Greg Maxey" wrote: Warrain, If you are using a simple Word document with a table then you can't enter raw numbers and have the come out formatted in real time. You can type your numbers as raw then select the appropriate cells and format all selected cells using a macro. Here is an example: Sub ConvertSelectedRawNumbersInTableToCurrencyFormat() If Not Selection.Information(wdWithInTable) Then MsgBox "Place cursor in a table cell or select multiple cells." Exit Sub End If Dim oCl As Word.Cell Dim oRng As Range Dim Count As Integer For Each oCl In Selection.Cells Set oRng = oCl.Range oRng.End = oRng.End - 1 With oRng If IsNumeric(oRng) Then .Text = FormatCurrency _ (Expression:=.Text, _ NumDigitsAfterDecimal:=2, _ IncludeLeadingDigit:=vbTrue, _ UseParensForNegativeNumbers:=vbTrue) End If ' If InStr(oRng.Text, "$") = False Then If IsNumeric(oRng) = False Then Count = Count + 1 End If End With Next oCl If Count = 0 Then MsgBox "Conversion complete." End If Selection.Collapse wdCollapseEnd If Count = 1 Then MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" End If If Count 1 Then MsgBox "" & Count & " of the selected cells are empty or content is not numerical. Conversion complete on all selected numerical cells.", , "Notice!!" End If End Sub -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Warrain wrote: I can see how to custom format the sum of a column of numbers but not how to format the raw numbers entered into the individual cells above. Be great if someone could put me on the right track please. Many thanks |
#4
|
|||
|
|||
How to format negative numbers in Word tables
Thanks again Greg.
How can I modify your macro so that numbers in a column line up when there is a mixture of some in brackets (negative) and some not please? "Greg Maxey" wrote: Warrain, If you are using a simple Word document with a table then you can't enter raw numbers and have the come out formatted in real time. You can type your numbers as raw then select the appropriate cells and format all selected cells using a macro. Here is an example: Sub ConvertSelectedRawNumbersInTableToCurrencyFormat() If Not Selection.Information(wdWithInTable) Then MsgBox "Place cursor in a table cell or select multiple cells." Exit Sub End If Dim oCl As Word.Cell Dim oRng As Range Dim Count As Integer For Each oCl In Selection.Cells Set oRng = oCl.Range oRng.End = oRng.End - 1 With oRng If IsNumeric(oRng) Then .Text = FormatCurrency _ (Expression:=.Text, _ NumDigitsAfterDecimal:=2, _ IncludeLeadingDigit:=vbTrue, _ UseParensForNegativeNumbers:=vbTrue) End If ' If InStr(oRng.Text, "$") = False Then If IsNumeric(oRng) = False Then Count = Count + 1 End If End With Next oCl If Count = 0 Then MsgBox "Conversion complete." End If Selection.Collapse wdCollapseEnd If Count = 1 Then MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" End If If Count 1 Then MsgBox "" & Count & " of the selected cells are empty or content is not numerical. Conversion complete on all selected numerical cells.", , "Notice!!" End If End Sub -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Warrain wrote: I can see how to custom format the sum of a column of numbers but not how to format the raw numbers entered into the individual cells above. Be great if someone could put me on the right track please. Many thanks |
#5
|
|||
|
|||
How to format negative numbers in Word tables
Greg, Debug is showing the following:
"Greg Maxey" wrote: Warrain, If you are using a simple Word document with a table then you can't enter raw numbers and have the come out formatted in real time. You can type your numbers as raw then select the appropriate cells and format all selected cells using a macro. Here is an example: Sub ConvertSelectedRawNumbersInTableToCurrencyFormat() If Not Selection.Information(wdWithInTable) Then MsgBox "Place cursor in a table cell or select multiple cells." Exit Sub End If Dim oCl As Word.Cell Dim oRng As Range Dim Count As Integer For Each oCl In Selection.Cells Set oRng = oCl.Range oRng.End = oRng.End - 1 With oRng If IsNumeric(oRng) Then .Text = FormatCurrency _ (Expression:=.Text, _ NumDigitsAfterDecimal:=2, _ IncludeLeadingDigit:=vbTrue, _ UseParensForNegativeNumbers:=vbTrue) End If ' If InStr(oRng.Text, "$") = False Then If IsNumeric(oRng) = False Then Count = Count + 1 End If End With Next oCl If Count = 0 Then MsgBox "Conversion complete." End If Selection.Collapse wdCollapseEnd If Count = 1 Then MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" End If If Count 1 Then MsgBox "" & Count & " of the selected cells are empty or content is not numerical. Conversion complete on all selected numerical cells.", , "Notice!!" End If End Sub -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Warrain wrote: I can see how to custom format the sum of a column of numbers but not how to format the raw numbers entered into the individual cells above. Be great if someone could put me on the right track please. Many thanks |
#6
|
|||
|
|||
How to format negative numbers in Word tables
Greg, Debug doesn't like the following line in your macro:
MsgBox "The selected cell is empty or content is not numerical.", , or the one following. "Greg Maxey" wrote: Warrain, If you are using a simple Word document with a table then you can't enter raw numbers and have the come out formatted in real time. You can type your numbers as raw then select the appropriate cells and format all selected cells using a macro. Here is an example: Sub ConvertSelectedRawNumbersInTableToCurrencyFormat() If Not Selection.Information(wdWithInTable) Then MsgBox "Place cursor in a table cell or select multiple cells." Exit Sub End If Dim oCl As Word.Cell Dim oRng As Range Dim Count As Integer For Each oCl In Selection.Cells Set oRng = oCl.Range oRng.End = oRng.End - 1 With oRng If IsNumeric(oRng) Then .Text = FormatCurrency _ (Expression:=.Text, _ NumDigitsAfterDecimal:=2, _ IncludeLeadingDigit:=vbTrue, _ UseParensForNegativeNumbers:=vbTrue) End If ' If InStr(oRng.Text, "$") = False Then If IsNumeric(oRng) = False Then Count = Count + 1 End If End With Next oCl If Count = 0 Then MsgBox "Conversion complete." End If Selection.Collapse wdCollapseEnd If Count = 1 Then MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" End If If Count 1 Then MsgBox "" & Count & " of the selected cells are empty or content is not numerical. Conversion complete on all selected numerical cells.", , "Notice!!" End If End Sub -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Warrain wrote: I can see how to custom format the sum of a column of numbers but not how to format the raw numbers entered into the individual cells above. Be great if someone could put me on the right track please. Many thanks |
#7
|
|||
|
|||
How to format negative numbers in Word tables
Warrain,
I am not ignoring you. I signe off shortly after posting and am now preparing fo work. When you post a macro to a newsgroup, text wrapping often messes up the longer lines. See if there is a spurious "-" in the line or try bringing the entire string together on one line. -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Warrain wrote: Greg, Debug doesn't like the following line in your macro: MsgBox "The selected cell is empty or content is not numerical.", , or the one following. "Greg Maxey" wrote: Warrain, If you are using a simple Word document with a table then you can't enter raw numbers and have the come out formatted in real time. You can type your numbers as raw then select the appropriate cells and format all selected cells using a macro. Here is an example: Sub ConvertSelectedRawNumbersInTableToCurrencyFormat() If Not Selection.Information(wdWithInTable) Then MsgBox "Place cursor in a table cell or select multiple cells." Exit Sub End If Dim oCl As Word.Cell Dim oRng As Range Dim Count As Integer For Each oCl In Selection.Cells Set oRng = oCl.Range oRng.End = oRng.End - 1 With oRng If IsNumeric(oRng) Then .Text = FormatCurrency _ (Expression:=.Text, _ NumDigitsAfterDecimal:=2, _ IncludeLeadingDigit:=vbTrue, _ UseParensForNegativeNumbers:=vbTrue) End If ' If InStr(oRng.Text, "$") = False Then If IsNumeric(oRng) = False Then Count = Count + 1 End If End With Next oCl If Count = 0 Then MsgBox "Conversion complete." End If Selection.Collapse wdCollapseEnd If Count = 1 Then MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" End If If Count 1 Then MsgBox "" & Count & " of the selected cells are empty or content is not numerical. Conversion complete on all selected numerical cells.", , "Notice!!" End If End Sub -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Warrain wrote: I can see how to custom format the sum of a column of numbers but not how to format the raw numbers entered into the individual cells above. Be great if someone could put me on the right track please. Many thanks |
#8
|
|||
|
|||
How to format negative numbers in Word tables
The following should be all on one line in the macro:
MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Warrain" wrote in message ... Greg, Debug doesn't like the following line in your macro: MsgBox "The selected cell is empty or content is not numerical.", , or the one following. "Greg Maxey" wrote: Warrain, If you are using a simple Word document with a table then you can't enter raw numbers and have the come out formatted in real time. You can type your numbers as raw then select the appropriate cells and format all selected cells using a macro. Here is an example: Sub ConvertSelectedRawNumbersInTableToCurrencyFormat() If Not Selection.Information(wdWithInTable) Then MsgBox "Place cursor in a table cell or select multiple cells." Exit Sub End If Dim oCl As Word.Cell Dim oRng As Range Dim Count As Integer For Each oCl In Selection.Cells Set oRng = oCl.Range oRng.End = oRng.End - 1 With oRng If IsNumeric(oRng) Then .Text = FormatCurrency _ (Expression:=.Text, _ NumDigitsAfterDecimal:=2, _ IncludeLeadingDigit:=vbTrue, _ UseParensForNegativeNumbers:=vbTrue) End If ' If InStr(oRng.Text, "$") = False Then If IsNumeric(oRng) = False Then Count = Count + 1 End If End With Next oCl If Count = 0 Then MsgBox "Conversion complete." End If Selection.Collapse wdCollapseEnd If Count = 1 Then MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" End If If Count 1 Then MsgBox "" & Count & " of the selected cells are empty or content is not numerical. Conversion complete on all selected numerical cells.", , "Notice!!" End If End Sub -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Warrain wrote: I can see how to custom format the sum of a column of numbers but not how to format the raw numbers entered into the individual cells above. Be great if someone could put me on the right track please. Many thanks |
#9
|
|||
|
|||
How to format negative numbers in Word tables
thanks Doug
"Doug Robbins" wrote: The following should be all on one line in the macro: MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Warrain" wrote in message ... Greg, Debug doesn't like the following line in your macro: MsgBox "The selected cell is empty or content is not numerical.", , or the one following. "Greg Maxey" wrote: Warrain, If you are using a simple Word document with a table then you can't enter raw numbers and have the come out formatted in real time. You can type your numbers as raw then select the appropriate cells and format all selected cells using a macro. Here is an example: Sub ConvertSelectedRawNumbersInTableToCurrencyFormat() If Not Selection.Information(wdWithInTable) Then MsgBox "Place cursor in a table cell or select multiple cells." Exit Sub End If Dim oCl As Word.Cell Dim oRng As Range Dim Count As Integer For Each oCl In Selection.Cells Set oRng = oCl.Range oRng.End = oRng.End - 1 With oRng If IsNumeric(oRng) Then .Text = FormatCurrency _ (Expression:=.Text, _ NumDigitsAfterDecimal:=2, _ IncludeLeadingDigit:=vbTrue, _ UseParensForNegativeNumbers:=vbTrue) End If ' If InStr(oRng.Text, "$") = False Then If IsNumeric(oRng) = False Then Count = Count + 1 End If End With Next oCl If Count = 0 Then MsgBox "Conversion complete." End If Selection.Collapse wdCollapseEnd If Count = 1 Then MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" End If If Count 1 Then MsgBox "" & Count & " of the selected cells are empty or content is not numerical. Conversion complete on all selected numerical cells.", , "Notice!!" End If End Sub -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Warrain wrote: I can see how to custom format the sum of a column of numbers but not how to format the raw numbers entered into the individual cells above. Be great if someone could put me on the right track please. Many thanks |
#10
|
|||
|
|||
How to format negative numbers in Word tables
This was very helpfull, but I just want to format as commas. How do I change
the script to either format only as commas or as currency without the $? "Warrain" wrote: thanks Doug "Doug Robbins" wrote: The following should be all on one line in the macro: MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Warrain" wrote in message ... Greg, Debug doesn't like the following line in your macro: MsgBox "The selected cell is empty or content is not numerical.", , or the one following. "Greg Maxey" wrote: Warrain, If you are using a simple Word document with a table then you can't enter raw numbers and have the come out formatted in real time. You can type your numbers as raw then select the appropriate cells and format all selected cells using a macro. Here is an example: Sub ConvertSelectedRawNumbersInTableToCurrencyFormat() If Not Selection.Information(wdWithInTable) Then MsgBox "Place cursor in a table cell or select multiple cells." Exit Sub End If Dim oCl As Word.Cell Dim oRng As Range Dim Count As Integer For Each oCl In Selection.Cells Set oRng = oCl.Range oRng.End = oRng.End - 1 With oRng If IsNumeric(oRng) Then .Text = FormatCurrency _ (Expression:=.Text, _ NumDigitsAfterDecimal:=2, _ IncludeLeadingDigit:=vbTrue, _ UseParensForNegativeNumbers:=vbTrue) End If ' If InStr(oRng.Text, "$") = False Then If IsNumeric(oRng) = False Then Count = Count + 1 End If End With Next oCl If Count = 0 Then MsgBox "Conversion complete." End If Selection.Collapse wdCollapseEnd If Count = 1 Then MsgBox "The selected cell is empty or content is not numerical.", , "Notice!!" End If If Count 1 Then MsgBox "" & Count & " of the selected cells are empty or content is not numerical. Conversion complete on all selected numerical cells.", , "Notice!!" End If End Sub -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. Warrain wrote: I can see how to custom format the sum of a column of numbers but not how to format the raw numbers entered into the individual cells above. Be great if someone could put me on the right track please. Many thanks |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can Wordperfect templates be converted to Word template docs | Terrill | New Users | 2 | September 14th, 2005 09:14 PM |
Underscore (_) will not always display in RTF files (Word 2002). | David A Edge | General Discussion | 6 | June 14th, 2005 10:39 AM |
Reveal Codes | BAW | General Discussion | 2 | June 7th, 2005 03:38 PM |
Basic macros query - where are they assigned to? | Bert Coules | New Users | 15 | January 18th, 2005 01:28 PM |
WordPerfect - copying formatting | Morgan | Page Layout | 1 | January 10th, 2005 06:00 PM |