MINVERSE()-- How to change array size?

[tolearn1977]

Hi I am working on a spreadsheet where I want to use built-in MINVERSE() function. However, my array (matrix) size can vary from 2*2 to 50*50. I am going to be using a square matrix; however the size will vary depending on the problem. Is it even possible to use a formula that will 'dynamically' change array size? Or this will require VBA coding? If so, would you be able to provide a sample code ?

 

[XOR LX]

Hi,

Not sure I understand. So, basically, you just want some code to take away the trouble of you having to manually select the appropriately-sized destination range in order to commit the array formula?

Regards

 

[tolearn1977]

Hi I am working on a spreadsheet where I want to use built-in MINVERSE() function. However, my array (matrix) size can vary from 2*2 to 50*50. I am going to be using a square matrix; however the size will vary depending on the problem. Is it even possible to use a formula that will 'dynamically' change array size? Or this will require VBA coding? If so, would you be able to provide a sample code ?

That's correct, I'm looking to be able to program Excel so it adjusts array size as matrix changes & then computes inverse using MINVERSE().
Regards

 

[XOR LX]

Perhaps:

Sub InverseArea()

Dim SourceCell As Variant, DestCell As Variant
Dim MatrixSize As Long
Dim SourceRange As Range, DestRange As Range

SourceCell = InputBox("Reference of top-left cell of Source Matrix")
MatrixSize = InputBox("Rows/Columns in Source Matrix")
DestCell = InputBox("Reference of top-left cell of Destination Matrix")

Set SourceRange = Range(SourceCell & ":" & Range(SourceCell).Offset(MatrixSize - 1, MatrixSize - 1).Address)
Set DestRange = Range(DestCell & ":" & Range(DestCell).Offset(MatrixSize - 1, MatrixSize - 1).Address)

DestRange.Select

Selection.FormulaArray = "=MINVERSE(" & SourceRange.Address(0, 0) & ")"

End Sub

Regards

 

[tolearn1977]

I tried your code & it works perfectly fine. Thanks a lot; truly appreciate your prompt help.

Regards

 

[XOR LX]

You're welcome. Thanks for the feedback.