I want to find the inverse of a table but the table definition is dynamic (with cells). How do I define it? Selection.FormulaArray = "=MINVERSE(perifA! Range(cells(1,1), cells(x,x)) Doesn't work. Any help
thanks
I want to find the inverse of a table but the table definition is dynamic (with cells). How do I define it? Selection.FormulaArray = "=MINVERSE(perifA! Range(cells(1,1), cells(x,x)) Doesn't work. Any help
thanks
In order to enter the formula you are thinking of you would need to use the ADDRESS() worksheet function. I think that would be rather messy. Therefore my code below employs named ranges instead.
Sub SetInverse()
' 16 Dec 2017
' specify the names of data and array ranges
Const DataRange As String = "InvData"
Const ArrayRange As String = "Minvers"
Dim DatRng As Range
Dim ArrRng As Range
Dim Sq As Long
On Error Resume Next
Set ArrRng = Range(ArrayRange)
ArrRng.ClearContents ' delete existing array formula
With ThisWorkbook
.Names(ArrayRange).Delete ' delete names
.Names(DataRange).Delete
End With
On Error GoTo 0
' place the data within the selected range
' select the columns starting from top left of intended range
With Selection
Sq = .Columns.Count ' number of selected columns
Set DatRng = .Cells(1).Resize(Sq, Sq)
End With
DatRng.Select ' auto-select the range
With Worksheets("Vinculator") ' define Array range on this sheet
Set ArrRng = Range(.Cells(1, 1), .Cells(Sq, Sq))
End With
With ThisWorkbook.Names ' [re]define the named ranges
.Add DataRange, DatRng
.Add ArrayRange, ArrRng
End With
ArrRng.FormulaArray = "=MINVERSE(" & DataRange & ")"
End Sub
There are two ranges, one holding the data, the other the array formula. The formula itself refers to the data range. The code resizes both according to the selection of data made. Be sure to start the column selection at the top left of your data range.