Can i declare variable sheetname in formula "if" in excel-CodePudding

Can i declare variable sheetname in formula "if" in excel This code get from vba macro.

ActiveCell.FormulaR1C1 = _
    "=IF('1'!RC='2'!RC,'1'!RC,""R1:""&'1'!RC&""|R2:""&'2'!RC)"

(1 and 2 is sheetname) I want to declare a variable, replacing 1 and 2. such as:

ActiveCell.FormulaR1C1 = _
    "=IF('i'!RC='i i'!RC,'i'!RC,""R1:""&'i'!RC&""|R2:""&'i i'!RC)"

CodePudding user response：

ActiveCell.FormulaR1C1 = _
    "=IF('" & i & "'!RC='" & i   i & "'!RC,'" & i & "'!RC,""R1:""&'" & i & "'!RC &""|R2:""&'" & i   i & "'!RC)"

CodePudding user response：

Your formula (without modification) results in a circular reference. So, it's hard to give you a "correct" answer as such.
But in essence, using (e.g.) the context of the first sheet name, you'd declare a string variable and set same to replace the reference to sheet '1'.

Assuming a viable result is to replace both sheet name and the space handling ', the simplest approach is something like:

Dim stShNm1$
stShNm1 = "'1'!"

Then change (e.g.):

"=IF('1'!RC

To:

"=IF(" &  stShNm1 & "RC

In this case of course, you's need variables (like) stShNm1 and stShNm2.
But the principle remains the same, throughout your R1C1 formula construction.