Currently I have it half working, but it clears my formula.
Sub Clear_Ranges()
Dim rng As Range
Set rng = Range("A4:E4, A9:E9")
For Each cell In rng
cell.Value = 0
cell.NumberFormat = "$#,##0.00"
Next
End Sub
garbo7441
Favorite Answer
As you did not indicate, in your original question, that your ranges contained formulas, I did not account for that when offering the above macro.
You really can't 'clears cells, but keep the formula'. As long as you have a formula, it is going to give you a result. Period. End of discussion. The only way to 'clear cells, but keep the formula' is to delete the data that the formula uses to perform its computation. In other words, clear cells A4:E4 and A9:E9. That will 'clear' the cell containing the formula, leaving the formula in place. If you can't, or don't want to, clear those ranges, there is nothing you can do.
Stu147
I think that you are clearing the cells with the command 'cell.Value = 0'. If you want to reset the formula to have no result then you'd clear one or more of the values that the formula is dependent upon, otherwise you'd have to implement something to stop the fomula calculating the result until you specify.
As long as the formula is in place and the values that it relies on are in place then it will calculate and show the results. Auto calculate can be turned off in the Excel options so you can stop it calculating without your input.
I can't see any other way around this.
JOSSDEAN
Hello
There is alwas a way to do things in Excel...PLEASE VOTE :)
you just need to evaluate your cells and that's it
For Each cell In rng
If Not cell.HasFormula Then
cell.Value = 0
cell.NumberFormat = "$#,##0.00"
End If
Next
if you need additional help, you can email me
pepedean@yahoo.com
:)