silentsound
02-18-2014, 04:34 AM
Hi everyone,
Hoping someone might be able to help me with this one. I want to set a custom format for some cells and used the macro recorder to work out what the custom format should look like (so I assume it should be correct formatting) however when I use the numberformatting in my code it causes an error. I am unclear exactly what the .locked and .formulahidden do so added bonus if anyone understands these (I just copied them from the recorder). I should add the numberformat below works correctly if I use it to set a custom format in Excel (without VBA)
This is the problematic code:
For j = 1 To 2
Worksheets(WSName(j)).Activate
With Application.FindFormat
.Clear
.NumberFormat = ReplaceFormatCell.NumberFormat
.Locked = True
.FormulaHidden = False
End With
With Application.ReplaceFormat
.Clear
.NumberFormat = "_(£* #,##0.00_);_(£* (#,##0.00);_(£* ""-""_);_(@_)"
.Locked = True
.FormulaHidden = False
End With
Cells.Replace "", "", xlPart, xlByRows, False, , True, True
To be clear it is the .Numberformat = "..." that causes the error. Any suggestions much appreciated!
Thanks
Hoping someone might be able to help me with this one. I want to set a custom format for some cells and used the macro recorder to work out what the custom format should look like (so I assume it should be correct formatting) however when I use the numberformatting in my code it causes an error. I am unclear exactly what the .locked and .formulahidden do so added bonus if anyone understands these (I just copied them from the recorder). I should add the numberformat below works correctly if I use it to set a custom format in Excel (without VBA)
This is the problematic code:
For j = 1 To 2
Worksheets(WSName(j)).Activate
With Application.FindFormat
.Clear
.NumberFormat = ReplaceFormatCell.NumberFormat
.Locked = True
.FormulaHidden = False
End With
With Application.ReplaceFormat
.Clear
.NumberFormat = "_(£* #,##0.00_);_(£* (#,##0.00);_(£* ""-""_);_(@_)"
.Locked = True
.FormulaHidden = False
End With
Cells.Replace "", "", xlPart, xlByRows, False, , True, True
To be clear it is the .Numberformat = "..." that causes the error. Any suggestions much appreciated!
Thanks