changeNumberFormat macro

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

Hi, I have a set of worksheets that i want to change the
number formatting on. In some columns there are
percentages, always with a header "%" and the rest I want
to be in accounting format but is in several different
number formats. Some cells are text only. I am working
on a macro to search the worksheet for cells that aren't
percentages and make them in the accounting format. Heres
what I have, can you see where I am going wrong?

Sub ChangeNumberFormat()
For Each cell In sht.UsedRange
IF Selection.NumberFormat = "0.00%"
End If
With Selection
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_
(* ""-""??_);_(@_)"
Next cell
MsgBox "Process Complete"
End Sub
 
You've kind of got a mixture of things going on.

Maybe this'll give you some more ideas:

Option Explicit
Sub ChangeNumberFormat()
Dim myCell As Range

For Each myCell In Selection.Cells 'sht.usedrange.cells
If myCell.NumberFormat = "0.00%" Then
'do nothing
Else
If IsNumeric(myCell.Value) Then
myCell.NumberFormat _
= "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End If
End If
Next myCell

MsgBox "Process Complete"
End Sub

I used selection (instead of .usedrange). I figured it would be easier for you
to limit the range by selecting the portions that could have number formats that
should be changed.
 
Sub ChangeNumberFormat()
Dim sh as Worksheet
Dim cell as Range
set sh = worksheets("Sheet1")
For Each cell In sht.UsedRange.SpecialCells(xlFormulas)
IF instr(cell.NumberFormat,"%") = 0 then
if cell.hasFormula then
if isnumeric(cell) then
cell.NumberFormat = "_(* #,##0.00_);" & _
"_(* (#,##0.00);_(* ""-""??_);_(@_)"
End If
End If
Next cell
MsgBox "Process Complete"
End Sub
 
Back
Top