jbesclapez
Active Member
- Joined
- Feb 6, 2010
- Messages
- 275
Hello,
I have this code working.
But instead of using :
'DB-SI'!R2C6:R358C6
I want to use a dynamic range, so here is what i prepared.
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.
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.