VBA keeps changing the formula I put into a range-CodePudding

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?

CodePudding user response：

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.