argument in vba with the help of the cells


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




Selected Answer

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
    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.



The code does not find the inverse(, he wants something else
george l (rep: 2) Dec 14, '17 at 7:22 am
I have a table on the page "", but each time has different dimensions, and I want to find the inverse on the "leontief" page. My code is like this:<br />Sub
george l (rep: 2) Dec 14, '17 at 11:41 am
When pasting code, please always add it to your original question where you can format it in a way I can read or copy/paste it.<br />I rewrote the code because I realised, while it contained an answer to your original question, it didn't live up to its appearance. It wasn't a plug-and-play procedure. The new code above is that. Let me know if it continues to give you problems.
Variatus (rep: 1328) Dec 14, '17 at 9:21 pm
when the program runs, on the command ".Add DataRange, DatRng"
george l (rep: 2) Dec 16, '17 at 12:45 am
This might be a version problem. I'm using Excel 2010, and when I "Add" a new named range using a name that already exists Excel will just change the address. Now I presume that this doesn't happen like that in Excel 2007, Therefore I amended the code above to first delete
Variatus (rep: 1328) Dec 16, '17 at 4:59 am
[argument in vba with the help of the cells] <br />In the line ".Names(ArrayRange).Delete"
george l (rep: 2) Dec 16, '17 at 7:11 am
Apparently my revised code got corrupted during upload which gave you nothing good to copy from. The error is caused by an extra "On Error Goto 0" you have in your version of the code. Just in case there are also other transscription errors, I suggest you copy the entire procedure again. I have fixed its formatting.
Variatus (rep: 1328) Dec 16, '17 at 9:59 pm
I just changed the code because it did not run, but it returns zero.The sheet "antist" is that we wand to write the inverse. Thanks fo your help<br />[argument in vba with the help of the cells]Code_Goes_Here<br />Sub minverse13()<br />Const DataRange As String = "InvData"<br />Const ArrayRange As String = "Minvers"<br />Dim DatRng
george l (rep: 2) Dec 17, '17 at 9:14 am
From the point of view of this Q&A forum your question has been answered. Our correspondence about how to make it work might only be of interest to other visitors if your requirement would be clear. For that purpose you need to attach a workbook which has a tab with the data range, showing some random data, and another sheet with the inversion range where you should show the desired result. The task would be to achieve the result with either code or formula.<br />Frankly, I wouldn't entertain such a task within the current thread. This thread shows how to write an array formula to a worksheet and it how to use the MINVERSE function. Both subjects are useful to many people and the answers can't be improved by further customisation. Therefore I suggest that you mark the answer as "selected", as a beacon to
Variatus (rep: 1328) Dec 17, '17 at 9:31 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login