Trying to insert a variable in a INDEX formula in VBA

jbesclapez

jbesclapez

Active Member
Joined
Feb 6, 2010
Messages
275
Hello,

I have this code working.
Code: 
    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.

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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
try

Code: 
[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]
 
Upvote 0
You can use:

Code: 
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.
 
Upvote 0
RoryA said:
You can use:

Rich (BB code): 
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.
Click to expand...

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 :
Code: 
    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;



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 :
Code: 
[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
 
Last edited:
Upvote 0
What was wrong with what I gave you previously that showed how to do it?
 
Upvote 0
RoryA said:
What was wrong with what I gave you previously that showed how to do it?
Click to expand...

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 :

Code: 
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Help! VBA Index and Match handling multiple criteria using array formula and variable in index range argument
Replies
0
Views
993
dylon25
D
K
  • Question Question
Variable losing set Range
Replies
8
Views
449
MARK858
MARK858
Z
  • Solved
VBA: Set HeaderRowRange as a Range Variable
Replies
3
Views
1K
kevin9999
K
C
  • Solved
index match excel formula to vba code
Replies
9
Views
332
DanteAmor
DanteAmor
E
  • Question Question
Trying to use index match method
Replies
7
Views
745
RoryA
RoryA

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top