A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Tables
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to format negative numbers in Word tables



 
 
Thread Tools Display Modes
  #1  
Old August 31st, 2005, 02:51 AM
Warrain
external usenet poster
 
Posts: n/a
Default 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  
Old August 31st, 2005, 03:10 AM
Greg Maxey
external usenet poster
 
Posts: n/a
Default 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  
Old August 31st, 2005, 04:27 AM
Warrain
external usenet poster
 
Posts: n/a
Default 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  
Old August 31st, 2005, 05:22 AM
Warrain
external usenet poster
 
Posts: n/a
Default 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  
Old August 31st, 2005, 05:45 AM
Warrain
external usenet poster
 
Posts: n/a
Default 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  
Old August 31st, 2005, 05:46 AM
Warrain
external usenet poster
 
Posts: n/a
Default 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  
Old August 31st, 2005, 08:29 AM
Greg Maxey
external usenet poster
 
Posts: n/a
Default 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  
Old August 31st, 2005, 08:18 PM
Doug Robbins
external usenet poster
 
Posts: n/a
Default 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  
Old September 1st, 2005, 07:44 AM
Warrain
external usenet poster
 
Posts: n/a
Default 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  
Old March 17th, 2006, 05:41 PM posted to microsoft.public.word.tables
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 03:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.