Reset Button Excel VBA - Value as Number not Text

0
votes

I have a spreadsheet and I want to reset some cells back to how they begin.

My Reset Button works using the following code.....

Sub Reset_Cells()
    'Updateby Extendoffice 20161008
     Range("D4", "D8").Value = "£0.00"
     Range("D11").Value = "£0.00"
End Sub

It works great but it inserts the £0.00 as text which effects a dependant cell with an IF statement (see below)

=IF(D4=0,0,IF(D4<=300,35,IF(D4<=500,50,IF(D4<=1000,70,IF(D4<=1500,80,IF(D4<=3000,115,IF(D4<=5000,205,IF(D4<=10000,455,IF(D4<=200000,ROUND(D4/100,2)*5,IF(D4>200000,10000))))))))))

When clicked the vlaues 'look' how they should but the dependant cell shows £10,000 (the highest IF option) instead of £0.00.

The resetted (is that even a word?!) cells have the green triangle in the corner which tell me the cell has a Number Stored as Text.

How can I fix this?

Thanks

1
vbaexcel
Apologies, I've fixed it. I just took the "" away and hey presto! You can see I'm a novice! - Ian Rimmer
So RubberDuck Debugging worked? - Vityata
Do you mean you removed the £ ? - CLR
I removed the apostrophe's from around the £0.00 leaving .Value = 0 only. This kept the cells formatting. - Ian Rimmer

1 Answers

1
votes

How about:

Sub Reset_Cells()
    With Range("D11,D4,D8")
        .Value = 0
        .NumberFormat = "£#,##0.00"
    End With
End Sub