Format Column as Decimal (VBA)

[David Schaleger]

I need VBA code to convert a column's (B, in this case) cell number format from the default "general" to 2-digit decimal. Thank you.

 

[vaskov17]

Range("B1").EntireColumn.NumberFormat = "#.00"

 

[David Schaleger]

Awesome! Thank you for the lightning fast reply!

 

[Rick Rothstein]

Range("B1").EntireColumn.NumberFormat = "#.00"

Since we are doing the whole column, it is simpler to just use Columns instead of Range...

Columns("B").NumberFormat = "0.00"

Note: I prefer using "0.00" instead of "#.00", hence my change in the assigned value.

 

[David Schaleger]

Thank you. How do I make this happen "on open"?

 

[David Schaleger]

Sorry, I should be more specific. The Excel file is created from an export utility. So the VBA code's probably going to have to go in the Personal Excel file. Whenever the exported file is opened, that's when the column needs to be formatted as decimal. The file will originally be exported as LTXNMMDD.csv, but part of an as-of-yet undetermined routine will rename the MMDD to the current 2-digit month and day. Not sure if this information is important or not, but there you go.

 

[Rick Rothstein]

Thank you. How do I make this happen "on open"?

This question has me a little confused. It would indicate that you always want Column B to have the cell number format of "0.00", but if that is the case, then why not simply select the column, apply the Cell Format normally (not with VB, but using Excel's Cell Format functionality) and Save the workbook? Once you do that, Column B will always have the indicated cell number format. Or are you saying your code or, heaven forbid, your users will change the columns cell format during its use?

 

[David Schaleger]

The idea is to have the exported file formated (decimal), renamed, then emailed to another user ready to upload into another system. We don't want anyone along the way to have to do anything manually. Unfortunaly, the export utility can't format the column, so it's up to VBA or, Heaven forbid, a user!