Excel VBA: Inserting an IF statement into a cell

[EverClear]

Hello!

As part of a macro, I have a formula in a cell that calculates the difference between 2 other cells. Now, I am trying to insert an IF statement into another cell that will look at this difference, and if the difference = 0, it will return the words "ON TARGET" in the cell. If the difference is greater than 0 it will return a message that says "OVER TARGET," and if it's less than 0, the returned message will be "Under Target." I really don't want to use a function for this. Here's the code I have so far:

ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C" '*Inserts a formula to calculate the difference between 2 cells
ActiveCell.Offset(0, 1).Select '*Moves 1 column to the right, on the same row

ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,""ON TARGET"",0)""",(IF(RC[-1]>0,"OVER TARGET",(IF(RC[-1]<0,"UNDER TARGET")))"


This third formula is the one that always blows up. I've tried writing this several ways, and I always get either a "compile error: Syntax error" message, or a "compile error: Expected end of statement" message.

When all is said and done the formula in the cell should look like:
=IF(R178=0,"ON TARGET",(IF(R178>0,"OVER TARGET",(IF(R178<0,"UNDER TARGET")))))

I obviously have too many (or not enough) "" marks, or too many/not enough ((())) in the VBA code, but after looking at this for hours, I can't tell where the problem is...

PLEASE HELP!!!!

 

[Andrew Poulsom]

Turn on the macro recorder and enter you formula in the appropriate cell. Turn off the recorder and you should have some code with the correct syntax.

 

[EverClear]

Why didn't I think of that?? Duh! Anyway, it worked - thanks a lot!!!