Excel vba Formula2R1C1 LET produces Runtime error 1004 using `r` or `c` as names-CodePudding

I wanted to insert a formula in a cell, using vba.

I first used the recorder for the formula and after being recorded ran the macro and it did not work.

I then tried modifying the recorded macro to avoid it from being a selection or active cell related issue. But no luck there.

I shortened the formula and found that the number of conditions seems to be related.

This is what I found:

Sub test()

ThisWorkbook.Worksheets("Sheet1").Range("D4").Formula2R1C1 = "=LET(d,R4C1:R1008C3,a,INDEX(d,,1),b,INDEX(d,,2),b)"

End Sub

This works without errors.

When I add another name to LET it errors:

Sub test()

ThisWorkbook.Worksheets("Sheet1").Range("D4").Formula2R1C1 = "=LET(d,R4C1:R1008C3,a,INDEX(d,,1),b,INDEX(d,,2),c,INDEX(d,,2),b)"

End Sub

This gives the following error:

I can't seem to understand what causes this error and I'm hoping somebody on here know what direction to think.

It's a Windows 11 Laptop (private) and running Office Version : 18.2301.1131.0

Edit: When adding option explicit it says:

CodePudding user response：

You need to avoid using c, C, r or R as variable names when writing in R1C1 notation as they represent the current Column (c) or Row (r) and so you're attempting to load a reference to a column into a parameter that is expecting a single variable name.