Run Time Error on Active formula in Excel Macro code

0
votes

I want to use the value of cell A5 of Sheet 2 to my active formula in macro in Sheet 1. However, I'm getting an

Error 1004 (application defined or object defined error).

If using a static value, for instance 100, instead of 'Sheet 2'!A5, it is working.

Below is my code:

ActiveCell.FormulaR1C1 = "=if('Sheet 2'!RC>'Sheet 2'!A5,""PASS"", ""FAIL"")"
1
vbaexcelexcel-formula

1 Answers

2
votes

You cannot mix-and-match xlA1 cell references with xlR1C1 cell references. A5 is R5C1 in xlR1C1 syntax.

ActiveCell.FormulaR1C1 = "=if('Sheet 2'!RC>'Sheet 2'!R5C1,""PASS"", ""FAIL"")"

Now in this formula, the RC is a relative row/relative column reference to the same cell on 'Sheet 2' that the active cell on the active sheet is on. However, the 'Sheet 2'!R5C1 reference will be absolute row/absolute column like 'Sheet 2'!$A$5.

If you require a relative row/relative column reference to 'Sheet 2'!A5 then you need to use .Formula instead of .FormulaR1C1 and reference the activecell's address in an xlA1 manner.

with ActiveCell
    .Formula = "=if('Sheet 2'!" & .address(0, 0) & ">'Sheet 2'!A5,""PASS"", ""FAIL"")"
end with