Multi tool use

Run Time Error on Active formula in Excel Macro code

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.

'Sheet 2'!A5

Below is my code:

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


1 Answer
1

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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.