Array formula into a range of cells
I am trying to fit an array formula into a range of cells ("B2: B10"). The formula should return multiple results, depending on the value in cell A2. When I do it the normal way (ctrl, shift, enter) it works fine, but when I try to do it with code it returns the same result in every cell that was found first. Can anyone help me get the result I am looking for?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("$A$2").Address Then
With Range("B2:B10")
.FormulaArray = "=INDEX(Absence!$C$2:$C$151, SMALL(IF($A$2=Absence!$A$2:$A$151, ROW(Absence!$A$2:$A$151)-ROW(Absence!$A$2)+1), ROW(Absence!1:1)))"
.Value = .Value
End With
End If
End Sub
source to share
This is better:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("$A$2").Address Then
Range("B2").FormulaArray = "=INDEX(Absence!$C$2:$C$151, SMALL(IF($A$2=Absence!$A$2:$A$151, ROW(Absence!$A$2:$A$151)-ROW(Absence!$A$2)+1), ROW(Absence!1:1)))"
Range("B2").Copy Range("B3:B10")
Range("B2:B10").Value = Range("B2:B10").Value
End If
End Sub
source to share
The problem is that you are an array entering the formula into all cells at once, not an array entering the first cell and filling. Without filling, ROW(1:1)
does not work. You should put all possible values k
for the function at SMALL
once with ROW(1:150)
.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("$A$2").Address Then
With Range("B2:B10")
.FormulaArray = "=INDEX(Absence!$C$2:$C$151, SMALL(IF($A$2=Absence!$A$2:$A$151, ROW(2:151)-ROW(2:2)+1), ROW(1:9)))"
.Value = .Value
End With
End If
End Sub
Btw, when we use ROW(Absence!$A$2:$A$151)
to achieve a number between 2 and 151, the worksheet and the column letter are unnecessary. ROW(2:151)
will execute the fine and clean up the formula a bit.
source to share