Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

argument in vba with the help of the cells

0

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

Answer
Discuss

Answers

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

Discuss

Discussion

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:
Sub ÌáêñïåíôïëÞ13()
Dim x As Integer, k As Integer Sheets("perifA").Select
Range("A1").CurrentRegion.Select
gram = Selection.Rows.Count
stiles = Selection.Columns.Count
Sheets("Leontief").Select
Range(Cells(1, 1), Cells(gram, stiles)).Select
k = gram
Selection.FormulaArray = "=MINVERSE(perifA!RC:R[k-1]C[k-1])" End Sub Any help thanks

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.
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: 4889) Dec 14, '17 at 9:21 pm
when the program runs, on the command ".Add DataRange, DatRng"  says "This command stop the debugger"   What is the wrong? thanks for your help   [argument in vba with the help of the cells]
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 both names, if they exist, and then create new ones. I hope this will solve the problem.
Variatus (rep: 4889) Dec 16, '17 at 4:59 am
[argument in vba with the help of the cells]
In the line ".Names(ArrayRange).Delete"      ' delete names   the program stops and the line turns yellow

[Sub setinverse10() 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
On Error GoTo 0
With ThisWorkbook
.Names(ArrayRange).Delete      ' delete names   
.Names(DataRange).Delete
End With
On Error GoTo 0
With Selection
Sq = .Columns.Count
Set DatRng = .Cells(1).Resize(Sq, Sq)
End With
DatRng.Select
With Sheets("Ypol")
Set ArrRng = Range(.Cells(1, 1), .Cells(Sq, Sq))
End With
With ThisWorkbook                ' [re]define the named ranges
.Add DataRange, DatRng
.Add ArrayRange, ArrRng
End With
ArrRng.FormulaArray = "=MINVERSE(" & DataRange & ")" End Sub]
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: 4889) 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
[argument in vba with the help of the cells]Code_Goes_Here
Sub minverse13()
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
Sq = InputBox("etetet")
With Selection
Set DatRng = .Cells(1).Resize(Sq, Sq)
End With
DatRng.Select
With Worksheets("antist")
Set ArrRng = Range(Cells(1, 1), Cells(Sq, Sq))
End With
With ThisWorkbook.Names
.Add DataRange, DatRng
.Add ArrayRange, ArrRng
End With
ArrRng.FormulaArray = "=MINVERSE(" & DataRange & ")"
End Sub
[]
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.
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 those who look for what is on offer here (and thank you for that). Then post your workbook, as described above, with another question, like, "How to invert a range based on selection". I promise, I shall look into it.
Variatus (rep: 4889) 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