Excel VBA - Out of memory when create a dynamic array formula

Question

I want to refer to a named range in cell A1 in another cell. Simple number arrays (1, 2, 3, and 4) make up the range, which I manually typed in C3:G3 and gave the name MyArray.

Excel will display a window stating "Out of Memory" when I run my function. Is there a solution to this issue?

Sub MyArray()

Range("A1").Formula2 = "=MyArray"


End Sub

Answer 1

Try this:

Sub MyArray()

Range("A1").Formula2 = "=R[2]C[2]:R[2]C[6]"

End Sub

But that will only put the C3:G3 in the A1 cell as you wanted. However when I just ran a macro replacing "=R[2]C[2]:R[2]C[6]" with "=C3:G3" and using A20 or something as a target cell, it worked just fine.