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.

 

[VBA Geek]

Hello, at first glance the things to adjust are

faddress=Range(ActiveCell.Offset(0,-15),ActiveCell.Offset(0,-15).End(xlUp).End(xlToRight).Offset(0, -4)).Address(ReferenceStyle:=xlR1C1)

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

also make sure that when you are returning the address to faddress you are setting the right values for ColumnAbsolute and RowAbsolute

 

[Christip]

Hi VBA Geek,
Thanks for your response. I tried inputting that reference style change but I still receive the same error message. Any other suggestions?

 

[VBA Geek]

make sure the formula is not missing any parenthesis or commas, copy the formula in the immediate window and try to paste it manually on the worksheet

 

[Christip]

I am sorry to be such a nag, but I copy pasted your formula from above just to be sure I didn't miss anything...I still get the Run Time Error 1004

 

[shg]

Before trying to put the formula in a cell, put it in a string variable and print it to the Immediate window. If it's correct, you should be able to copy it from there and paste it into the cell (after putting Excel in R1C1 mode). If not, you're hot on the trail.

 

[Christip]

Works fine in the immediate window, but still out of luck. Thanks for your assistance.

 

[Christip]

The problem is when I enter it manually in the worksheet it works fine. But when I try to run it with VBA that's when it fails the second version of the formula. All the string variables are verified.

 

[VBA Geek]

What do you get when you print it in the immediate window?

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

also what is the activecell?

 

[Christip]

My active cell is in column w, the row is dynamic.

When I print this in the immediate window = "=IF(RC[-19]=R[-1]C[-19]),if(countif(" & faddress &",""hrcmp"")>0,IF(COUNTIF(" & faddress & ",""hrpay"")>0,""HRCMP/HRPay Mix"","""")),""""))"

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