Formatting a cell to Currency (w/ 0 Decimal Places) in VBA

[mangoel]

Hi All,

I need to save a value into a certain cell, formatting it as currency with 0 decimal places. How do I do this?

I know of the Format function, but don't know how to control the decimal places.

-
Activesheet.Cells(1,2) = Format(ListPrice,"currency")
-

I cannot pre-format the cell using the Excel menu because my code requires all cells in the worksheet to be Cleared (Using "Clear All") before this statement. I'd really applreciate any help on this. Thanks!

Manish

 

[Corticus]

try adding this code to the worksheet:
Sub Workbook_Open()


Sub Workbook_Open()

Cells.Select
Selection.NumberFormat = "#,##0"
Range("A1").Select
Application.CalculateFull
End Sub

and if you need a symbol, substitute this in:

Selection.NumberFormat = "$#,##0"

-Corticus

 

[Barry Katcher]

You can get the code Corticus sent you, and much, much more, by recording a keyboard macro.

Click on Tools-Macro-Record New Macro-O.K.
Click on Format-Cells-Number-Currency and set the decimal place to zero.
Click on Tools-Macro-Stop Recording.

Walla, mansewer!