CODEWITHSUNDEEP
excelexcel-formulavba
Anurag
Anurag

Reputation: 19

How to Format numbers in a column as per currency standards using excel vba?

I want to format all cells of a column. The data is a number and I want to add a decimal from left keeping only two digits after that. Then format it as per currency standards. For eg:

Data:       Output:
10000       100.00
112233      1,122.33
123456789   1,234,567.89

The requirement is normal and output is in italics.

Upvotes: 0

Views: 11495

Answers (2)

Vityata
Vityata

Reputation: 43585

Macro recorder is a bit dangerous in this case, because if you use it, your code would look like this (from the other answer):

Sheets("Sheet1").Columns("A").NumberFormat = "£#,##0.00"

However, this is only ok in UK. If you want to write code, which formats currency based on the local settings of the PC, then it is a good idea to use the currency there:

Sheets("Sheet1").Columns("A").Style = "Currency"

Then a UK person would get pounds format and a US one dollars.

Upvotes: 2

Xabier
Xabier

Reputation: 7735

The following will format a given column as Currency:

Sheets("Sheet1").Columns("A").NumberFormat = "£#,##0.00"

Upvotes: 4

Related Questions