Formatting with the $ dollar sign symbol

[sherobot]

Does anybody know of a way to instantly format numbers so that if you had
$5,000,000
$50
$5,000
....and they had to be center aligned in a column, a way to have the $ signs
line up and the zeros line up?
ex:
$5,000,000
$ 50
$ 5,000

right now I'm using spaces and when you have 80 rows this can be a pain in
the neck. Using Ctrl+tab would be just as bad. Any suggestions? macros or
field code that would do this trick for me. I was hoping maybe a macro that
could find the largest number, count the number of spaces needed in each cell
of the column...and then it would put the correct amount of spacing in.
thanks

 

[Karl E. Peterson]

Does anybody know of a way to instantly format numbers so that if you
had $5,000,000
$50
$5,000
...and they had to be center aligned in a column, a way to have the $
signs line up and the zeros line up?
ex:
$5,000,000
$ 50
$ 5,000

right now I'm using spaces and when you have 80 rows this can be a
pain in the neck. Using Ctrl+tab would be just as bad. Any
suggestions? macros or field code that would do this trick for me. I
was hoping maybe a macro that could find the largest number, count
the number of spaces needed in each cell of the column...and then it
would put the correct amount of spacing in. thanks

Yeah, you'll need to determine the Max first. Do you know how to do that?
Assuming you do, you'd just do something like:

' Determine max number of chars
Dim FldLen As Long
FldLen = Len(Format$(MaxValue, "#,##0"))

Then, output each like this:

"$" & LPad$(Format$(ThisValue, "#,##0"), FldLen)

Where:

Private Function LPad(ByVal Text As String, ByVal Width As Long) As
String
LPad = Right$(Space$(Width) & Text, Width)
End Function

Make sense?

 

[sherobot]

Thanks Karl. Sounds good. But I am new to the programming world and don't
know how to determine the Max characters for a column.

 

[Karl E. Peterson]

Thanks Karl. Sounds good. But I am new to the programming world and
don't know how to determine the Max characters for a column.

Ah, well, as a programmer, your main goal is to break a task into the *most*
atomic steps you can. In this case, there are two steps:

* gather the data into a structure that can be iterated
* iterate the data to find the maximum

The ideal structure for this would be an array of some sort. (I don't know
whether the column in your case could be thought of in this way, but if it
is you're all set!) Then you just loop through each element of your
dataset, keeping track of what you've found so far.

Let's say your data is stored in an array called MyData...

For i = LBound(MyData) To UBound(MyData)
If MyData(i) > TheMax Then
TheMax = MyData(i)
End If
Next i

I'm more of a ClassicVB programmer, and not that familiar with the Word
object model (I'm assuming you're working in Word, given the group, right?).
If you can't adapt the above to your data structure, perhaps someone else
here would have further suggestions.

 

[sherobot]

Yes I'm working with Word VB editor. Thank you for putting me in the right
direction on this. Will try to get more info on programming in tables