Macro Assessment Revisted

G

Greg

Hi,

Several months ago Dave Lett and Bruce Brown and I worked
on a macro to convert raw numbers in a table to currency
format. Well actually Dava and Bruce worked and I learned.

I was wondering if anyone can have a look at this code and
see if there is a way to a. work out a small bug, and b.
refine it to act only on selected cells.

a. The small bug appears when there is a single non-
numerical character in the cell. This character is not
marked as non-numerical. I know the reason for this is
the line

If oRng.Characters.Count = 1 Then GoTo EndSmallLoop

Unfortunately if the Count is set to = 0 then the message
box alerts on all the empty cells as well. I can't find a
way to ignore empty cells while alerting on single non-
numerical characters.

b. The other issue is if you run this macro it converts
all numbers. If you have a table with ProductID,
Nomenclature, Unit Cost, Quantity Ordered, Total Cost,
etc. the macro converts all pure numerical numbers i.e. if
the quantity orderd is 6 it changes to $6.00. I would
like to find a way to make the macro act only on cells
physcially selected (highlighted) with the mouse or cursor.

Any help is appreciated. I will not be able to check back
here probably until Monday so don't think I am ignoring
your offer of assistance.



Sub ConvertRawNumbersToCurrencyFormat()

If Not Selection.Information(wdWithInTable) Then
MsgBox "Cursor must be in a table.", , "Quitting"
End
Else
Selection.MoveRight wdCharacter
End If

Dim oCl As Cell
Dim oRng As Range
Dim Rw As Row
Dim R As Range
Dim TableNo As Integer

Set R = Selection.Range
R.Start = ActiveDocument.Range.Start
TableNo = R.Tables.Count

For Each Rw In ActiveDocument.Tables(TableNo).Rows
If Rw.Index = 1 Then GoTo EndBigLoop
For Each oCl In Rw.Cells
Set oRng = oCl.Range
With oRng
.MoveEnd Unit:=wdCharacter, Count:=-1

If IsNumeric(oRng) Then
.Text = FormatCurrency _
(Expression:=.Text, _
NumDigitsAfterDecimal:=2, _
IncludeLeadingDigit:=vbTrue, _
UseParensForNegativeNumbers:=vbTrue)
End If

If oRng.Characters.Count = 1 Then GoTo
EndSmallLoop
On Error GoTo EndSmallLoop
' CATCH ERRORS HERE
If InStr(oRng.Text, "$") = False Then
oRng.Font.Color = wdColorRed
oRng.Select
MsgBox "Cell content is not
numerical.", , "Error"
Selection.Collapse wdCollapseEnd
End If
EndSmallLoop:
End With
Next
EndBigLoop:
Next
End Sub
 
C

Cindy Meister -WordMVP-

Hi Greg,
I would
like to find a way to make the macro act only on cells
physcially selected (highlighted) with the mouse or cursor.
Then I'd change this

For Each Rw In ActiveDocument.Tables(TableNo).Rows

To something more like this

For Each cel in Selection.Cells
Unfortunately if the Count is set to = 0 then the message
box alerts on all the empty cells as well. I can't find a
way to ignore empty cells while alerting on single non-
numerical characters.
Try setting to <= 1 so that it also traps 0

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan 24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
 
C

Cindy Meister -WordMVP-

Hi Greg,
tried several
different of the For each cel... but the results were
either errors or would not return the desired result.
The basic code to process each cell in a selection is:

Sub ProcessEachCellInSelection()
Dim cel As Word.Cell

For Each cel In Selection.Cells
cel.Range.Text = "Hi"
Next cel
End Sub

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Jan 24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail
:)
 
G

Greg Maxey

Cindy,

Thanks. Your prod was just enough to let me see a solution.
For anyone interested the following macro will convert selected cell in a
table to currency format. There may be some dead wood in the code and I
welcome any comments that will lead to refinement.



--
\\\\///////////
( @ @ )
----oo00---(_)---00oo----
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL
 
G

Greg Maxey

The following macro meaning the following macro:

Sub ConvertSelectedRawNumbersInTableToCurrencyFormat()

If Selection.Type = wdSelectionIP Then
MsgBox "Select a cell or range of cells before running this macro.", ,
"Nothing Selected"
End
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 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 "" & Count & " of the selected cells is empty or content is
not numerical. Conversion complete on all selected numerical cells.", ,
"Notice!!"
End If
End Sub

--
\\\\///////////
( @ @ )
----oo00---(_)---00oo----
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL
 
M

Mark Tangard

Hi Greg,

It's pretty neat. The only thing I'd change -- and this isn't
anything that would speed it up -- is the selection-checker at
the beginning. To avoid throwing an error if the cursor isn't
in a table, I'd add:

If Not Selection.Information(wdWithinTable) Then
MsgBox "[scold here]"
Exit Sub
End If

That's perhaps more important than having a cell(s) selected.
In fact, in that connection, over time I've changed many of
my editing macros that once required the user to begin with
a non-IP selection, since those macros often get heavy use,
and users may tire of having to 'prime' it by selecting text
first when the target is obvious (ok, obvious to the human).
So my laziness-based instinct is to have the code select the
current cell if the selection is the IP (and is in a table).
The macro can then run on the current cell only and the user
is saved the most uncomfortabe step.

--
Mark Tangard <[email protected]>, Microsoft Word MVP
Please reply only to the newsgroup, not by private email.
Note well: MVPs do not work for Microsoft.
"Life is nothing if you're not obsessed." --John Waters
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top