Link to home
Start Free TrialLog in
Avatar of Joe Gantenbein
Joe Gantenbein

asked on

How to use Dynamic Ranges in Array Formulas using VBA

I am trying to write a macro that runs a regression analysis on a set of random variables and calculates the variance for a confidence interval. Specifically, I am trying to calculate [X'X]^-1. In this instance, X is a (# * 6) matrix and # varies depending on how much data the user has available. I have been successful using a static range, but am unable to figure out how to make it dynamic. The static code that works currently is:

Sheets("Forecast Calculations").Range("P2:U7").FormulaArray = _
     "=MINVERSE(MMULT(TRANSPOSE('Data for Regression'!R2C2:R172C7), 'Data for Regression'!R2C2:R172C7))"

My latest attempt at making it dynamic is as such:

Sheets("Data for Regression").Select
Dim RegDataEnd As Long
RegDataEnd = Columns("A").Find(what:="", after:=Cells(1, 1), LookIn:=xlValues).row - 1

Sheets("Forecast Calculations").Range("P2:U7").FormulaArray = _
    "=MINVERSE(MMULT(TRANSPOSE(Sheets("Data for Regression").Range(Cells(2, 2), Cells(RegDataEnd, 7))), Sheets("Data for Regression").Range(Cells(2, 2), Cells(RegDataEnd, 7)))"

But I receive the following error message:

Expected End of Statement:

And it highlights "Data in the Sheets() that follows transpose. Is there any way to do what I am trying to do?
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try

Sheets("Forecast Calculations").Range("P2:U7").FormulaArray = _
     "=MINVERSE(MMULT(TRANSPOSE('" & Sheets("Data for Regression").Name & "'!" & _
     Sheets("Data for Regression").Range(Cells(2, 2), Cells(RegDataEnd, 7)).Address & _
     "),'" & Sheets("Data for Regression").Name & "'!" & _
     Sheets("Data for Regression").Range(Cells(2, 2), Cells(RegDataEnd, 7)).Address & "))"

Open in new window

Regards
Avatar of Joe Gantenbein

ASKER

Rgonzo1971,

That solved the "Expected End of Statement" error. However, I am now receiving "Run-time error '1004': Application-defined or object-defined error". You wouldn't happen to know what would be causing that, do you?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.