mikey38654
New Member
- Joined
- Jun 15, 2018
- Messages
- 9
Good morning community,
As a novice VBA programmer I was fortunate enough to discover quickly enough for my project that the correct way to clear/reset cells which are calculated in a formula is not to set the value, ie:
Range.Value = ""
Do not do this if cell is referenced in a formula elsewhere, or the formula will not work properly until you update the cells you cleared this way with a new number.
Instead, you will want to:
Range.ClearContents
...which will clear the number or formula out of the cell but retain formatting instructions, including conditional formatting, and not confuse Excel to think it's a string and ignore it in math calculations. It will look at cells cleared with value="" as strings even though the Ribbon may say "Number" when the cell is selected.
As a novice VBA programmer I was fortunate enough to discover quickly enough for my project that the correct way to clear/reset cells which are calculated in a formula is not to set the value, ie:
Range.Value = ""
Do not do this if cell is referenced in a formula elsewhere, or the formula will not work properly until you update the cells you cleared this way with a new number.
Instead, you will want to:
Range.ClearContents
...which will clear the number or formula out of the cell but retain formatting instructions, including conditional formatting, and not confuse Excel to think it's a string and ignore it in math calculations. It will look at cells cleared with value="" as strings even though the Ribbon may say "Number" when the cell is selected.
