Formula works in Excel but not in VBA

[Christip]

The following formula works fine in vba:
faddress=Range(ActiveCell.Offset(0,-15),ActiveCell.Offset(0,-15).End(xlUp).End(xlToRight).Offset(0, -4)).Address
ActiveCell.FormulaArray = "=if(countif(" & faddress & ",""hrcmp"")>0,IF(COUNTIF(" & faddress & ",""hrpay"")>0,""HRCMP/HRPay Mix"",""""),"""")"

However, when I change the formula to this the 1004 error code occurs. I am sure it has something to do with mixing formula styles don't know how to fix it.

ActiveCell.Formula = "=IF(RC[-19]=R[-1]C[-19]),if(countif(" & faddress
&",""hrcmp"")>0,IF(COUNTIF(" & faddress & ",""hrpay"")>0,""HRCMP/HRPay Mix"","""")),""""))"

Please help... thank you in advance.

 

[shg]

This

=IF(RC[-19]=R[-1]C[-19]),if(countif(,"hrcmp")>0,IF(COUNTIF(,"hrpay")>0,"HRCMP/HRPay Mix","")),""))

... is not a valid formula. For one thing, the COUNTIF requires a range argument, and there isn't one.

I would start by posting the R1C1 formula that actually works when you enter it manually, and say where it's entered.

 

[Christip]

I am sorry if I'm not conveying my issue correctly and thanks for being patient but,

The formula =IF(RC[-19]=R[-1]C[-19]),if(countif(,"hrcmp")>0,IF(COUNTIF(,"hrpay")>0,"HRCMP/HRPay Mix","")),"")) was the result in the Immediate Window from what you had told me to do in your earlier post

MY formula that I need assistance with is
ActiveCell.Formula = "=IF(RC[-19]=R[-1]C[-19]),if(countif(" & faddress & ",""hrcmp"")>0,IF(COUNTIF(" & faddress & ",""hrpay"")>0,""HRCMP/HRPay Mix"","""")),""""))"

As I mentioned in my earlier post, this formula works when I enter it manually in my workbook, however, not through VBA.

Also, if I leave the formula as: ActiveCell.FormulaArray = "=if(countif(" & faddress & ",""hrcmp"")>0,IF(COUNTIF(" & faddress & ",""hrpay"")>0,""HRCMP/HRPay Mix"",""""),"""")" in VBA. it runs perfect.

It is only breaking down when I add "=IF(RC[-19]=R[-1]C[-19]), to the beginning of the formula.

Hope this makes sense.

 

[shg]

I would put Excel in R1C1 mode and record a macro while inserting a formula that works.

Then write code to do whatever variable substitution is necessary into the recorded formula. And, again, test by writing the formula to the Immediate window, and then pasting it in the cell to see if it works, and if not, see what's wrong. If you keep trying to push it in with VBA, you're flying blind.