Array formula into range of cells

3
votes

I'm trying to put an array formula into a range of cells ("B2:B10") The formula should return multiple results dependent on the value in cell A2. When I do it the normal way (ctrl, shift, enter) it works ok, but when I try to do it with code it returns the same result in each cell which is the first instance found. Can anyone help me out to get the result I'm 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
2
vbaexcel
Can you show us how "it works okay" and how it doesn't work with the code? - CallumDA
When i put the formula into the cell manually and copy it down the cells I get the multiple different results I am looking for. When i use the code above it returns 1 value, the first value found and puts it in each cell. Hope that explains it. - Sherbetdab

2 Answers

2
votes

Is this any 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
2
votes

The problem is that you are array-entering the formula into all of the cells at once instead of array-entering into the first cell and filling down. Without filling down, the ROW(1:1) does not progess. You need to put all of the possible k values for the SMALL function in at 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 column letter are not necessary. ROW(2:151) will do fine and cleans up the formula a little.