Apply a formula to a cell using a VBA macro?

[mnuttall87]

Quick question relating to VBA. I have a sheet of financial statement data where cells contain text, such as 1.46M, or 14.0K, etc. I would prefer that each individual cell contained the actual value, so the 1.46M cell should show 1,460,000 and the 14K cell should show 14,000.


I came up with an =IF formula that reads something like:

=IF(RIGHT(C5)="M",LEFT(C5,(LEN(C5)-1))*1000000,IF(RIGHT(C5)="K",LEFT(C5,(LEN(C5)-1))*1000,IF(RIGHT(C5)="B",LEFT(C5,(LEN(C5)-1))*1000000000,IF(C5="","",0))))


It basically checks the letter in the cell and multiplies the cell by the correct number.


Is there a way that I can select all of the financial data, and then have VBA say:


For Each Cell in Selection


'Apply above =If Formula to Cells.Value and Paste as Value


Next Cell


Or something along those lines?


Thanks in advance for any help.

 

[Hui]

Mnuttall87


Firstly, Welcome to the Chandoo.org forums


What you need to use here is Custom Number formats


These are accessed by selecting the cell and Right Click

Format Cells

Number Tab

Custom Category


In the Type: Box enter

[>1000000]#,###.0,,"M";[>1000]#,###.0,"k";#,###.0


This will display

12,345,000 as 12.3M

and

12,345 as 12.3k


Read more about custom number formats here:

http://chandoo.org/wp/tag/custom-number-format/

http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/

http://www.ozgrid.com/Excel/excel-custom-number-formats.htm

or

http://www.ozgrid.com/Excel/CustomFormats.htm

 

[mnuttall87]

Awesome tip -- hadn't even thought of that.


Thanks for the quick reply!


Long time stalker on this forum, but I've been able to answer most of my problems myself or by reading other posts. Thanks again.

 

[mnuttall87]

Hui -- is there a way to program that custom format backwards? For example if the cell already has 12.3k in it, it can read 12,300 when it is done?


Thank you in advance.

 

[Hui]

It does

Assuming your cell is A2

In a blank cell =A2

and apply a , Number format


The Cell is displaying say 12.3K but still maintains the value 12,300 inside