Re: Formating output.

From: Ron Rosenfeld (ronrosenfeld_at_nospam.org)
Date: 11/27/04 

Date: Sat, 27 Nov 2004 18:55:45 -0500

On Sat, 27 Nov 2004 18:14:08 -0500, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>On Sat, 27 Nov 2004 13:23:24 -0700, "JustSomeGuy" <nope@nottelling.com> wrote:
>
>>I have a number which is the size of a file on disk.
>>Depending on the size of the number I want to display it as either
>>1.5KB, or 2.0MB or .75GB or .5TB
>>
>>However I want to be able to add up all the numbers in that column as normal
>>numbers and also format it the same way.
>>
>>How do I do this.... I do know VB very well...
>>
>>Brian.
>>
>>
>>
>
>Well, since you know VB well, that would be one way to do it. You could use an
>event macro, and format the cell depending on the contents.
>
>With this method, though, you cannot get fractions of a nB.
>
>For example:
>
>================
>Private Sub Worksheet_Change(ByVal Target As Range)
>Dim aoi As Range
>Dim c As Range
>Set aoi = [a1:a100]
>
>For Each c In aoi
> Select Case c.Value
> Case Is >= 10 ^ 12
> c.NumberFormat = "0,,,,.00 \T\B"
> Case Is >= 10 ^ 9
> c.NumberFormat = "0,,,.00\G\B"
> Case Is >= 10 ^ 6
> c.NumberFormat = "0,,.00\M\B"
> Case Is >= 10 ^ 3
> c.NumberFormat = "0,.00\K\B"
> Case Else
> c.NumberFormat = "General"
> End Select
>Next c
>
>End Sub
>=========================
>--ron

Sorry, I was wrong about fractions of an nB. Use this routine:

=====================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim c As Range
Set aoi = [a1:a100]

For Each c In aoi
    Select Case c.Value
        Case Is >= 0.5 * 10 ^ 12
            c.NumberFormat = "0.00,,,, \T\B"
        Case Is >= 0.5 * 10 ^ 9
            c.NumberFormat = "0.00,,,\G\B"
        Case Is >= 0.5 * 10 ^ 6
            c.NumberFormat = "0.00,,\M\B"
        Case Is >= 0.5 * 10 ^ 3
            c.NumberFormat = "0.00,\K\B"
        Case Else
            c.NumberFormat = "General"
    End Select
Next c

End Sub

======================

--ron