Search code examples
excelformattingcustom-formattingvba

custom formatting macro: need to print single digit integers with a decimal point

Im working on a wee macro to output files as a prn which will be used as an input for other software. I've had everything working properly, the only problem is that the other software requires all the integers to be printed with a decimal point.

Using excel 2007 btw

Ie if the cell value is 0 it should be printed as "0." not blank. likewise if the value is 8, it should be printed as "8." this isnt a problem with most of the values as the vast majority are like 123.45765 etc.

The code tells me its expecting a ) after the decimal point in .NumberFormat(""0."") on line 4

Windows("bdf_generator").Activate
With Worksheets(2).Range("E2:H9592").FormatConditions _
.Add(xlCellValue, xlBetween, "=0", "=9")
FormatConditions(1).NumberFormat=(""0."")
With Selection.FormatConditions(1).StopIfTrue = False
End With
End With
Solution

  • In your case, the line FormatConditions(1).NumberFormat=(""0."") is incorrect due to the two double quotes ("") surrounding 0.. Also note that FormatConditions(1).NumberFormat = "0." should be prepended with Selection. or else it would throw an error in runtime. Try this instead, as I believe it is clearer (and you get intellisense):

    Windows("bdf_generator").Activate
Dim r As Excel.Range
Set r = Worksheets(2).Range("E2:H9592")
Dim fc As Excel.FormatCondition
Set fc = r.FormatConditions.Add(xlCellValue, xlBetween, "=0", "=9")
fc.NumberFormat = "0."
fc.StopIfTrue = False