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:U 7").Formul aArray = _
"=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:U 7").Formul aArray = _
"=MINVERSE(MMULT(TRANSPOSE (Sheets("D ata 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?
Sheets("Forecast Calculations").Range("P2:U
"=MINVERSE(MMULT(TRANSPOSE
My latest attempt at making it dynamic is as such:
Sheets("Data for Regression").Select
Dim RegDataEnd As Long
RegDataEnd = Columns("A").Find(what:=""
Sheets("Forecast Calculations").Range("P2:U
"=MINVERSE(MMULT(TRANSPOSE
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?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
pls try
Open in new window
Regards