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?