Macro debug

[dsimmons83]

Hello again. I'm trying to create a macro that will reset a dashboard. So after a user is done adding their values, they can run the macro and it will give them a fresh dashboard. Could someone please let me know what I'm doing wrong? The first range will blank out some values. The second range needs to return a formula (this is what is creating the error). I'm completely new to macros and I'm trying to make my life easier. Thanks!

Sub Reset()
Range("C8,C9,C2").Value = ""
Range("E7").Formula = "=IF(SUM(C2:D6)=0,"",Formulas!H16)"
End Sub

 

[Joe4]

The issue is that your formula has double-quotes in it, and double-quotes are text delimiters in VBA.
So you need to double it up in VBA, like this:

Sub Reset()
    Range("C8,C9,C2").Value = ""
    Range("E7").Formula = "=IF(SUM(C2:D6)=0,"""",Formulas!H16)"
End Sub

 

[dsimmons83]

Awesome, thank you. Your code works but I don't understand why. In the IF statement, for the false option a "" should return a blank. My equation works when it isn't include in the macro. Could you please explain the need for the double quotes?

 

[Joe4]

Because your formula is enclosed in double-quotes.

In VBA, when denoting literal text, you use double-quotes as your text delimiter.

Try this simple test. Here is a one-line macro that returns a Message Box.

Sub MyTest()
    MsgBox "This is a test"
End Sub

Run that, and see what happens.

Now what if you try that without quotes, i.e.

Sub MyTest()
    MsgBox This is a test
End Sub

What happens?

If you don't use double-quotes to indicate literal text, it thinks it should be variables, functions, properties, etc., so you will get errors when it cannot find any of those things.
Note when using text qualifiers, every starting double-quote needs an ending one. Because your formula has double-quote surrounding it:

[COLOR="#FF0000"]"[/COLOR]=IF(SUM(C2:D6)=0,[COLOR="#FF0000"]"[/COLOR][COLOR="#0000FF"]"[/COLOR],Formulas!H16)[COLOR="#0000FF"]"[/COLOR]

it thinks that you are ending the double-quotes and starting them up again. It is treated the double-quotes in the formula as text qualifiers instead of literal double-quotes (because how is it to know the difference, which one you are intending). Doubling it up like that gets around that issue.

Note that whenever you have a formula like this not working the way you want, there is a simply solution. Turn on your Macro Recorder, and record yourself entering the formula into any cell. Then stop the Macro Recorder and look at the code that was just created. This will show you how that part needs to look. So let Excel do the work for you (note that the formula will probably look a bit different, as it will use R1C1 range formatting, but you can see how it handles that double-quote empty string).