Macro Error - Custom Cell Formatting

[CubicleRot]

I use several macros to help me convert numbers that do not work when I use custom cell formatting.

Below is an example of the VBA I use to multiply a cell value by 1000 or add formula to do so if formula is present.


Sub MUL1000()

Dim cell As Range
For Each cell In Selection
If Len(cell.Value) > 0 And Application.IsNumber(cell.Value) Then
If cell.HasFormula Then
cell.Formula = Replace(cell.Formula, "=", "=1000*(") & ")"
Else
cell.Value = 1000 * cell.Value
End If
End If
Next

End Sub


This code works great until I use custom formatting. Below is the custom formatting where I am encountering the error. The formatting is necessary to show a number that is in whole dollars in 1,000s.

_($* #,##0,_);_($* (#,##0,);_($* "-"??_);_(@_)


Please take it easy on me, I'm a novice. Thanks!

 

[Gerald Higgins]

Hi, welcome to the board.

I'm not a VBA expert, but just playing around with the macro recorder, I came up with this . . .

Sub MUL1000()

Dim cell As Range
For Each cell In Selection
If Len(cell.Value) > 0 And Application.IsNumber(cell.Value) Then
If cell.HasFormula Then
cell.Formula = Replace(cell.Formula, "=", "=1000*(") & ")"
cell.NumberFormat = "_(\$* #,##0,_);_(\$* (#,##0,);_(\$* ""-""??_);_(@_)"
Else
cell.Value = 1000 * cell.Value
cell.NumberFormat = "_(\$* #,##0,_);_(\$* (#,##0,);_(\$* ""-""??_);_(@_)"

End If
End If
Next

End Sub

There may well be better ways of doing this . . .