CODEWITHSUNDEEP
vbaexcel
Raul Gonzales
Raul Gonzales

Reputation: 906

converting bytes to kB and MB pending of the cell data in vba

Simple question: Lets say i have got 2 cells (A1 and A2). on A1 i have a value of 16000000 and on cell A2 a value of 230000. both values are in bytes. what i need is to be able to convert them to MB and kB respectively with VBA code.

i can start dividing A1/1024/1024 and A2/1024 but what i would like to know is how do i make the vba code diferentiate between the 2 values and then convert them to their respective category. i could convert both of them to MB by dividing by 1024 but that would leave me with cell A2 with a value of 0.23MB. i am not asking for code to be written for me, just for an explanation on how to do this if possible.

Upvotes: 0

Views: 531

Answers (1)

user4039065
user4039065

Reputation:

If you can handle using 10³ (aka 1,000) as a Kb instead of 2¹⁰ (aka 1,024) and 10⁶ (aka 1,000,000) as a Mb instead of 2²⁰ (aka 1,048,576) then a Custom Number Format Code will preserve the raw underlying value while displaying the unit of size.

range("B2:B99").NumberFormat = "[Color10][>999999]0,, \M\b;[Color13][>999]0, K\b;[Color3]0 \B;"

I have also added blue for Mb, green for Kb and red for B.

MB_KB_B

Upvotes: 1

Related Questions