VBA Code to Change Portion of Array Formula

Hi all,

How is everyone doing? Well I have been exhausted with a workbook I have been working on for quite some time now, and hope I can get some help here.

I have the following array formula in Sheet1, S2 and copied down to S10051:

{=IF('Sheet2'!$AP$5="",IF(ROWS($A$2:$A2)>T$1,"",INDEX($G$2:$G$10051,SMALL(IF($K$2:$K$10051=O$1,IF($Y$2:$Y$10051=S$1,ROW($K$2:$K$10051)-ROW($K$2)+1)),ROWS($A$1:$A1)))),"")}

I am trying to have the ‘Sheet2’!$AP$5=”” portion to be relative to any changed cell in range H5:H15001 of Sheet2 whose value changes. For example, when cell H15 is changed, the formula should change to {=IF(‘Sheet2’!$AP$15=”” . . .; when cell H200 is changed, the formula should change to {=IF(‘Sheet2’!$AP$200=”” . . . etc. Concurrently, I want the value of that changed cell place in cell O1 of Sheet1, and have the workbook be updated.

Nothing I tried is working.

Any help will be much appreciated.

Gos-C

as a heads up you may find the easiest way to do this is to use a Worksheet_Change event on Sheet2 based on your H range changing, at that point you can use the Formula range on Sheet1 S2:S10051 and update the FormulaArray using a Replace to update the Formula.

Something along the lines of the below perhaps:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H5:H15001")) Is Nothing Then Exit Sub
Dim ws1 As Worksheet: Set ws1 = Sheets("sheet1")
Dim rng_ws1 As Range: Set rng_ws1 = ws1.Range("S2:S10051")
Dim str_fm As String, str_fm_temp As String
str_fm = rng_ws1.Cells(1, 1).Formula
str_fm_temp = Left(str_fm, InStr(2, str_fm, "=") - 1)
str_fm = Replace(str_fm, str_fm_temp, "=IF(Sheet2!$AP$" & Target.Row)
rng_ws1.FormulaArray = str_fm
ws1.Cells(1, "O") = Target.Value
Set rng_ws1 = Nothing
Set ws1 = Nothing
End Sub

This may not work -- I can't test on your sample obviously... I suspect given use of relative row referencing it may need a tweak (ie enter in R1C1 style)

I will look in again tomorrow in case no one else picks up.

The below uses a helper cell with INDIRECT +helper cell:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CrntCellAddr As String

    Const ColumnH As String = "H5:H15001"
    Const TempAddr = "AA1"
    
    CrntCellAddr = Target.Address(False, False)
        
        If Not Intersect(Range(ColumnH), Range(CrntCellAddr)) Is Nothing Then
            Range(TempAddr) = "Sheet2!$AP$" & Target.Row
        End If
End Sub

=IF(INDIRECT(AA1)="",IF(ROWS($A$2:$A2)>T$1,"",INDEX($G$2:$G$10051,SMALL(IF($K$2:$K$10051=O$1,IF($Y$2:$Y$10051=S$1,ROW($K$2:$K$10051)-ROW($K$2)+1)),ROWS($A$1:$A1)))),"")

Hi DonkeyOte,
Hi protonLeah,

Thank you for your reply.

I am unable to get either of the solutions to work.

With DonkeyOte's code, all of the Relative cell references are being replaced as Absolute all the way down; and, with protonLeah, I am getting #REF! error.

Regards,
Gos-C

Did you remember to mod the formula and macro to reflect your actual helper cell?
GOS-C-(TEST).xls

Hi protonLeah,

H5:H15001 is on Sheet2 so I had placed the code in Sheet2 worksheet module, but you have H5:H15001 and the code in Sheet1. Even when I placed the code in Sheet1 worksheet module, it still didn't work.

Also, I want the value of the changed cell (H5:H15001) placed in cell O1 of Sheet1.

Can you please advise further.

Thank you,
Gos-C