VBA Array Formula

[pwill]

Hi, can anyone help with this Array Formula?

I have formulas that I add to sheet1 through vba that adds rows from columns "A", "B" & "E:K" to columns "AI","AJ" & "AK:AQ" and then apply autofill down to the number of rows required. The formula for Columns "AK:AQ" puts the values in order small to large from columns "E:K"

Column “A2:A” has Dates
Column “B2:B” has Ref No’s 1 to 10
Columns “E2:K” have numerical values

.Formula = "=(A" & r & ")"
.Formula = "=(B" & r & ")"
.FormulaArray = "=SMALL(E" & r & ":K" & r & ",{1,2,3,4,5,6,7})"

This Sub works fine when autofill is applied and adds 10 rows to columns "AI","AJ" & "AK:AQ" from columns "A", "B" & "E:K

[LEFT][COLOR=#222222][FONT=Verdana]Sub addrows[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Dim r As Long[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]r = 2[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Range("AI2"),Formula = "=(A" & r & ")"[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Range("AJ2"),Formula = "=(B" & r & ")"[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Range("AK2:AQ2").FormulaArray = "=SMALL(E" & r & ":K" & r & ",{1,2,3,4,5,6,7})"[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]r = 11[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Range("AI2:AQ2").AutoFill Destination:=Range("AI2:AQ" & r), Type:=xlFillDefault[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR][/LEFT]

I want the same results without the autofill and have tried the following code. Columns “AI” & “AJ” apply correctly by increasing one row at a time but I can’t get the Array Formula to apply from “AK2” through to “:AQ11” it applies the formula ok to “AK2:AQ2” but “AK3” through to “:AQ11” just repeats “AK2:AQ2 instead of increasing each row as with the rows in columns “AI” & “AJ”

[LEFT][COLOR=#222222][FONT=Verdana]Sub addrows[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Dim r As Long[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]r = 2[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Range("AI2:AI11"),Formula = "=(A" & r & ")"[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Range("AJ2:AJ11"),Formula = "=(B" & r & ")"[/FONT][/COLOR][/LEFT]

[COLOR=#ff0000][LEFT]Range("AK2:AQ11").FormulaArray = "=SMALL(E" & r & ":K" & r & ",{1,2,3,4,5,6,7})"[/LEFT]
[/COLOR]

[LEFT][COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR][/LEFT]

If I change the line in red to ‘Range("AK2:AQ11").Formula = "=(E" & r & ")" I get the correct results and increases one row at time the same as columns “AI” & “AJ” but it does not order the values small to large.

Any help would be appreciated

Regards

pwill

 

[Domenic]

With array formulas, first you need to enter the formula in the first row (ie. AK2:AQ2), and then you can copy the formula down to AK11:AQ11, for example...

With Range("AK2:AQ11")
    .Rows(1).FormulaArray = "=SMALL(E" & r & ":K" & r & ",{1,2,3,4,5,6,7})"
    .FillDown
End With

Hope this helps!

 

[pwill]

With array formulas, first you need to enter the formula in the first row (ie. AK2:AQ2), and then you can copy the formula down to AK11:AQ11, for example...

With Range("AK2:AQ11")
    .Rows(1).FormulaArray = "=SMALL(E" & r & ":K" & r & ",{1,2,3,4,5,6,7})"
    .FillDown
End With

Hope this helps!

Thanks Domenic, that does help, I thought it would be different for Array formulas, just needed clarification. Much appreciated

pwill

 

[Domenic]

You're very welcome!

Cheers!