On Sat, 27 Nov 2004 18:14:08 -0500, Ron Rosenfeld
wrote:

On Sat, 27 Nov 2004 13:23:24 -0700, "JustSomeGuy" 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