Re: Formating output.

From: Peter T (peter_t_at_discussions)
Date: 11/28/04 

Date: Sun, 28 Nov 2004 18:37:12 -0000

"Rob van Gelder" <newsgroups@nojunkmail-vangelder.co.nz> wrote in message
news:#sAcHyN1EHA.2876@TK2MSFTNGP12.phx.gbl...
> Pretty neat stuff with the ,,, business.

Nothing like a good comma here and there...

> Just to add a layer of complexity...
> There is 1024 bytes to a kilobyte
> 1024 kilobytes to a megabyte
> 1024 megabytes to a gigabyte

Oh dear!
Another one for Brian to play with:

Sub FileFormat2()
Dim sCustom As String
For Each r In Selection
Select Case r.Value
Case Is >= 2 ^ 40: sCustom = "#,,,,"" Tb"""
Case Is >= 2 ^ 39: sCustom = """.5 Tb"""
Case Is >= 2 ^ 30: sCustom = "#,,,"" Gb"""
Case Is >= 2 ^ 29: sCustom = """.5 Gb"""
Case Is >= 2 ^ 20: sCustom = "#,,"" Mb"""
Case Is >= 2 ^ 19: sCustom = """.5 Mb"""
Case Is >= 2 ^ 10: sCustom = "#,"" Kb"""
Case Is >= 2 ^ 9: sCustom = """0.5 Kb"""
Case Else: sCustom = """ <0.5 Kb"""
End Select
r.NumberFormat = sCustom
Next
'for quarters use 2^8, 2^18 etc & add to 2^9 etc for 3/4
End Sub

Brian, will need to run custom code each time any of your numbers change. As
Ron mentioned a macro triggered by an appropriate "Event" is convenient to
make it run automatically when required, and on the correct range of cells.
You might have a look at Chip Pearson's introduction:
http://www.cpearson.com/excel/events.htm

If you don't need to format your values and can accept to display in a
separate list, maybe something like this:

Sub FileFormat3()
Dim r As Range, rng As Range
Dim n As Single, s As String, v

v = Array(" B", " Kb", " Mb", " Gb", " Tb")
Set rng = Selection

For Each r In rng
    For i = 40 To 10 Step -10
        If r >= 2 ^ i Then
            Exit For
        End If
    Next
    n = r / (2 ^ i)
    If i Then s = "##,##0.0" Else s = "##,##0 "
    s = Format(n, s & v(i / 10))
    r.Offset(0, 1) = s 'place in adjacent cell

    'r.NumberFormat = Chr(34) & s & Chr(34)
    'Theoretically possible, but quickly builds up unwanted custom formats
    'unless deleted at top of routine. Also there's a limit to total custom
formats.
Next

End Sub

Probably best to discard a workbook used for testing custom number formats,
or delete all the unused ones.

Regards,
Peter

>
> --
> Rob van Gelder - http://www.vangelder.co.nz/excel
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:eD3MXaN1EHA.3500@TK2MSFTNGP09.phx.gbl...
> > Brian,
> >
> > I think you should be able to format as you wish and retain values for
> > summing.
> >
> > For starters, but not quite what you want:
> >
> > Sub FileFormat()
> > Dim sCustom As String, r as Range
> >
> > For Each r In Selection
> > Select Case r.Value
> > Case Is >= 10 ^ 9: sCustom = "#.###,,,"" Gb"""
> > Case Is >= 10 ^ 6: sCustom = "#.###,,"" Mb"""
> > Case Is >= 10 ^ 3: sCustom = "#.###,"" Kb"""
> > Case Else: sCustom = "###"" B"""
> > End Select
> > r.NumberFormat = sCustom
> > Next
> >
> > End Sub
> >
> > I'll leave you to adapt to your needs, say:
> > change the case values to adapt to your .5 & .75 etc
> > reduce the "###", or
> > remove the ".",
> > replace ### with ".5Kb" or whatever.
> > maybe change alignment a little with some spaces in the string
> > Add more case's to suit.
> >
> > When done maybe you can put an event routine.
> >
> > Regards,
> > Peter
> >
> > "JustSomeGuy" <nope@nottelling.com> wrote in message
> > news:uHAKq#L1EHA.2716@TK2MSFTNGP14.phx.gbl...
> >> 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.
> >>
> >
> >
>
>