VBA - Arrayformula to a range stored as array variable

Question

I need to apply a formula array to dynamic ranges in my spreadsheets, which for a fixed size range works fine using for example:

Selection.Offset(2, 0).FormulaArray = "=(SUM((Exx:Eyy-Fxx:Fyy)^2)/X)^.5"

But the ranges don't have a fixed size and position in all my spreadsheets, so I select them initially and then I store them as arrays of dimension 1 and then redim to length N.

These arrays will be named, for example, A for the range Exx:Eyy and B for the range Fxx:Fyy, so then, when I want to apply the same aforementioned array formula, I get the error Compile Error: type mismatch when using the array variables instead of the explicit ranges with:

Selection.Offset(2, 0).FormulaArray = "=(SUM((" & A & "-" & B & ")^2)/X)^.5"

And if I don't use the quotes and ampersand, of course, I got the error name.

I also tried to use the evaluate property on the right hand side of the equality and it works without errors, but in this way I don't get the same value as using formula array :/

Can anyone give me a hand please?

Answer 1

It's not clear from your question what is the data type of your arrays string / range? It seems that excel

• either don't know which sheet do you mean (if your reference is stored as a string, than add sheet to the address too (e.g. sheet1!a1:b1))
• either excel misinterpret your data (if your reference is stored in range try to use A.address instead of just A)