Trying to insert a variable in a INDEX formula in VBA

[jbesclapez]

Hello,

I have this code working.

    Range("A2").Select
    Selection.FormulaArray = _
        "=INDEX('DB-SI'!R2C6:R358C6,SMALL(IF(FREQUENCY(MATCH('DB-SI'!R2C6:R358C6,'DB-SI'!R2C6:R358C6,0),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROWS(R2C:RC)))"

But instead of using :
'DB-SI'!R2C6:R358C6
I want to use a dynamic range, so here is what i prepared.

Dim lastRowA As Long
With ActiveSheet
    lastRowA = Worksheets("DB-SI").Range("E4").Value
End With


Dim lastCol2 As Long
With ActiveSheet
    lastCol2 = Worksheets("DB-SI").Range("E6").Value
End With


Dim rngRow As Range: Set rngRow = Application.Range("A2" & lastRowA)
Dim rngCol As Range: Set rngCol = Application.Range("B1" & lastCol2)
Dim rngUnion As Range
Set rngUnion = Application.Union(Arg1:=rngRow, Arg2:=rngCol)






    Range("A2").Select
    Selection.FormulaArray = _
        "=INDEX("&rngRow&",SMALL(IF(FREQUENCY(MATCH('DB-SI'!R2C6:R358C6,'DB-SI'!R2C6:R358C6,0),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROWS(R2C:RC)))"

But I am getting an error in my VBA. No idea why. I tested the range with
rngRow.value="TEST" and it worked....

So please how can I use this kind of formula in VBA with a dynamic range?

Thanks for taking time to answer my problem.

 

[Truiz]

try

[COLOR=#333333]"=INDEX(" & rngRow.Address & ",SMALL(IF(FREQUENCY(MATCH('DB-SI'!R2C6:R358C6,'DB-SI'!R2C6:R358C6,0),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROWS(R2C:RC)))"[/COLOR]

 

[RoryA]

You can use:

Dim sRng as String
sRng = "'" & rngRow.Worksheet.name & "'!" & rngRow.Address(referencestyle:=xlr1c1)
Range("A2").FormulaArray = _
        "=INDEX(" & srng & ",SMALL(IF(FREQUENCY(MATCH(" & srng & "," & srng & ",0),ROW(" & srng & ")-ROW('DB-SI'!R2C6)+1),ROW(" & srng & ")-ROW('DB-SI'!R2C6)+1),ROWS(R2C:RC)))"

You could also add another variable for the address of the first cell in the range.

 

[jbesclapez]

You can use:

Dim sRng as String
sRng = "'" & rngRow.Worksheet.name & "'!" & rngRow.Address(referencestyle:=xlr1c1)
Range("A2").FormulaArray = _
        "=INDEX(" & srng & ",SMALL(IF(FREQUENCY(MATCH(" & srng & "," & srng & ",0),ROW(" & srng & ")-ROW('DB-SI'!R2C6)+1),ROW(" & srng & ")-ROW('DB-SI'!R2C6)+1),ROWS(R2C:RC)))"

You could also add another variable for the address of the first cell in the range.

Truiz, Rory,

Truiz: Thanks for spotting the space error. It was a stupid syntax error from me... but still i can not make the macro i need.
Rory: Please could you have a look at the macro below.

Basically I have a working macro but with FIXED range :

    Range("A2").Select
    Selection.FormulaArray = _
        "=INDEX('DB-SI'!R2C6:R358C6,SMALL(IF(FREQUENCY(MATCH('DB-SI'!R2C6:R358C6,'DB-SI'!R2C6:R358C6,0),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROWS(R2C:RC)))"
    Selection.AutoFill Destination:=Range("A2:A5"), Type:=xlFillDefault
    Range("A2:A5").Select
    Range("B1").Select
    Selection.FormulaArray = _
        "=INDEX('DB-SI'!R2C7:R358C7,SMALL(IF(FREQUENCY(MATCH('DB-SI'!R2C7:R358C7,'DB-SI'!R2C7:R358C7,0),ROW('DB-SI'!R2C7:R358C7)-ROW('DB-SI'!R2C7)+1),ROW('DB-SI'!R2C7:R358C7)-ROW('DB-SI'!R2C7)+1),COLUMNS(RC2:RC)))"
    Selection.AutoFill Destination:=Range("B1:Z1"), Type:=xlFillDefault
    Range("B1:Z1").Select
    Range("B2").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX('DB-SI'!R2C8:R358C8,MATCH(RC1&R1C,'DB-SI'!R2C6:R358C6&'DB-SI'!R2C7:R358C7,0)),"""")"
    Selection.AutoFill Destination:=Range("B2:Z2"), Type:=xlFillDefault
    Range("B2:Z2").Select
    Selection.AutoFill Destination:=Range("B2:Z5"), Type:=xlFillDefault
    Range("B2:Z5").Select

And I am trying to make it dynamic in the formula. No success so far. Here is my code;

Sub Macro21()
'
' Macro19 Macro
'


Dim lastRowA As Long
With ActiveSheet
    lastRowA = Worksheets("DB-SI").Range("E4").Value
End With


Dim lastCol2 As Long
With ActiveSheet
    lastCol2 = Worksheets("DB-SI").Range("E6").Value
End With


'Define the Range to copy to
Dim rngRow As Range: Set rngRow = Range("A2" & lastRowA + 1)
Dim rngCol As Range: Set rngCol = Range("B1" & lastCol2 + 1)




Dim lastRowSI As Long
With ActiveSheet
    lastRowSI = .Cells(.Rows.Count, "F").End(xlUp).Row
End With




Dim rngMaladie As Range: Set rngMaladie = Worksheets("DB-SI").Range("F2:F" & lastRowSI)
Dim rngQR As Range: Set rngQR = Range("G2:G" & lastRowSI)
Dim rngComment As Range: Set rngComment = Range("H2:H" & lastRowSI)




'    Selection.FormulaArray = _
        "=INDEX('DB-SI'!R2C6:R358C6,SMALL(IF(FREQUENCY(MATCH('DB-SI'!R2C6:R358C6,'DB-SI'!R2C6:R358C6,0),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROWS(R2C:RC)))"






    Range("A2").Select
    


    Selection.FormulaArray = _
        "=INDEX('DB-SI'!R2C6:R358C6,SMALL(IF(FREQUENCY(MATCH('DB-SI'!R2C6:R358C6,'DB-SI'!R2C6:R358C6,0),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROWS(R2C:RC)))"






    
 '   Range("A2").Select
    
    
    
'    Selection.FormulaArray = _
'        "=INDEX(" & rngMaladie & ",SMALL(IF(FREQUENCY(MATCH(" & rngMaladie & "," & rngMaladie & ",0),ROW(" & rngMaladie & ")-ROW('DB-SI'!R2C6)+1),ROW(" & rngMaladie & ")-ROW('DB-SI'!R2C6)+1),ROWS(R2C:RC)))"
'    Selection.AutoFill Destination:=Range("A2:A" & lastRowA), Type:=xlFillDefault
 '   Range("B1").Select
 '   Selection.FormulaArray = _
 '       "=INDEX(" & rngQR & ",SMALL(IF(FREQUENCY(MATCH(" & rngQR & "," & rngQR & ",0),ROW(" & rngQR & ")-ROW('DB-SI'!R2C7)+1),ROW(" & rngQR & ")-ROW('DB-SI'!R2C7)+1),COLUMNS(RC2:RC)))"
 '   Selection.AutoFill Destination:=Range("B1:Z1"), Type:=xlFillDefault
 '   Range("B1:Z1").Select
 '   Range("B2").Select
 '   Selection.FormulaArray = _
 '       "=IFERROR(INDEX(" & rngComment & ",MATCH(RC1&R1C," & rngMaladie & "&" & rngQR & ",0)),"""")"
 '   Selection.AutoFill Destination:=Range("B2:Z2"), Type:=xlFillDefault
 '   Range("B2:Z2").Select
 '   Selection.AutoFill Destination:=Range("B2:Z5"), Type:=xlFillDefault
 '   Range("B2:Z5").Select
End Sub

Anymore help please?

Can we please focus on that line :

[B]  Selection.FormulaArray = _[/B]
[B]        "=INDEX('DB-SI'!R2C6:R358C6,SMALL(IF(FREQUENCY(MATCH('DB-SI'!R2C6:R358C6,'DB-SI'!R2C6:R358C6,0),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROW('DB-SI'!R2C6:R358C6)-ROW('DB-SI'!R2C6)+1),ROWS(R2C:RC)))"[/B]

I will then try to make it work untill the end.

Thanks

 

[RoryA]

What was wrong with what I gave you previously that showed how to do it?

 

[jbesclapez]

What was wrong with what I gave you previously that showed how to do it?

Hi Rory,

Thanks to your info I did it. It took me a while to understand your code. I changed few things and it works now :

Here is my result :

Dim lastRowSI As Long
With ActiveSheet
    lastRowSI = Worksheets("DB-SI").Cells(.Rows.Count, "F").End(xlUp).Row
End With


Dim rngMaladie2 As String
rngMaladie2 = "'DB-SI'!" & Range("F2:F" & lastRowSI).Address(ReferenceStyle:=xlR1C1)


' ITS DYNAMIC VERSION


    Range("A2").Select
    Selection.FormulaArray = _
        "=INDEX(" & rngMaladie2 & ",SMALL(IF(FREQUENCY(MATCH(" & rngMaladie2 & "," & rngMaladie2 & ",0),ROW(" & rngMaladie2 & ")-ROW('DB-SI'!R2C6)+1),ROW(" & rngMaladie2 & ")-ROW('DB-SI'!R2C6)+1),ROWS(R2C:RC)))"

However, it is running super slowly. We are talking minutes here.
The result in the cell is :
=INDEX('DB-SI'!$F$2:$F$41413;SMALL(IF(FREQUENCY(MATCH('DB-SI'!$F$2:$F$41413;'DB-SI'!$F$2:$F$41413;0);ROW('DB-SI'!$F$2:$F$41413)-ROW('DB-SI'!$F$2)+1);ROW('DB-SI'!$F$2:$F$41413)-ROW('DB-SI'!$F$2)+1);ROWS(A$2:A2)))

Can we speed this up? and how?