So I have a relatively simple code that puts a formula (that refers to a different sheet in the same workbook) into a range.
Range(Selection, Selection.End(xlUp)).Formula2R1C1 = "=XLOOKUP(RC[-1],'Trade Summary'!X:X,'Trade Summary'!H:H)"
My problem is that after I run the code VBA changes the formula into for example:
=XLOOKUP(E15,'Trade Summary'!X:(X),'Trade Summary'!H:(H))
which makes it not working due to the brackets around ranges.
Is there a way to avoid this?
>Solution :
It seems strange, but you use C24 to refer to all of column X and C8 to refer to all of column H when using R1C1 notation. So adjust your formulas as follows:
"=XLOOKUP(RC[-1],'Trade Summary'!C24,'Trade Summary'!C8)"
Here C24 does not mean the cell at the intersection of column C and Row 24, it means "Column 24" which is all of column X.