VBA to replace cells containing specific text in a large range with an array formula

[DaveBre]

Hi,

I've got a large specific range of cells (C4:AZ10000) where some contain the text 'Check'.

Where each cell contains 'Check" I'd like it replaced with an array formula (below):

=IFERROR(INDEX('Leave'!$G$1:$G$10432,MATCH(1,IF('Leave!$A$1:$A$10432=$A8,IF(AC$1>='Leave!$I$1:$I$10432,IF(AC$1<='Leave'!$I$1:$I$10432,1))),0)),"Check")

Unfortunately using find and replace doesn't work because of the need to close the formula with SHIFT + CNTRL + ENTER.

I also can't drag the formula across the range as I need the formula to return 'Check' under the IFERROR conditions if the Index fails.

I've search but can't find any suitable solutions.

Any help would be appreciated.

Thanks

David

[jindon]

Perhaps

Sub test()
    Dim r As Range, ff As String
    With Range("C4:AZ10000")
        Set r = .Find("Check", , , 2)
        If Not r Is Nothing Then
            ff = r.Address
            Do
                r.FormulaArray = "=IFERROR(INDEX('Leave'!$G$1:$G$10432,MATCH(1,IF('Leave'!$A$1:$A$10432=$A8,IF(AC$" & r.Row & ">=" & _
                                 "'Leave'!$I$1:$I$10432,IF(AC$" & r.Row & "<='Leave'!$I$1:$I$10432,1))),0)),""Check"")"
                Set r = .FindNext(r)
            Loop Until (ff = r.Address) + (r Is Nothing)
        End If
    End With
End Sub

[DaveBre]

Thanks very much jindon,

It almost works.

Within my formula the row reference is absolute and needs to remain at row 1 which contains a date (AC$1), but the column changes. When I run the code the row moves.

Any idea how to fix that?

Thanks

David

[jindon]

OOps, missed this.

but the column changes. When I run the code the row moves.

When you say "column changes", how do you want it to change?
Can you show me the example?
e.g
If "Check" is found in Col.C then formula =
If Col.D then formula =

etc...

[jindon]

Ok, then try change to your original formula. (missing few ' before !)

r.FormulaArray="=IFERROR(INDEX('Leave'!$G$1:$G$10432,MATCH(1,IF('Leave'!$A$1:$A$10432=$A8,IF(AC$1>=" & _
"'Leave'!$I$1:$I$10432,IF(AC$1<='Leave'!$I$1:$I$10432,1))),0)),"Check")

[DaveBre]

Hi jindon,

Unfortunately I still can't get the formula to move across the rows and columns.

I've attached a small sample file, would you mind having a look?

Thanks

David

[jindon]

Missed double quotation for ""Check"" in the formula and need to add a bit of trick.

Sub test()
    Dim r As Range, ff As String, flg As Boolean
    With Range("C4:AZ10000")
        Set r = .Find("Check", , xlValues, 2)
        If Not r Is Nothing Then
            Do
                r.FormulaArray = "=IFERROR(INDEX('Leave'!$G$1:$G$10432,MATCH(1,IF('Leave'!$A$1:$A$10432=$A8,IF(AC$1>=" & _
                                "'Leave'!$I$1:$I$10432,IF(AC$1<='Leave'!$I$1:$I$10432,1))),0)),""Check"")"
                If (Not flg) * (r.Value = "Check") Then ff = r.Address: flg = True
                Set r = .FindNext(r)
                If r Is Nothing Then Exit Do
            Loop Until ff = r.Address
        End If
    End With
End Sub

[DaveBre]

Hi jindon,

Unfortunately the formula still references only cell $A8 and AC$1 regardless of where it is on the range, where it needs to ref whatever row it's on for A (4-10000) etc and whatever column it's on C-AZ.

Thanks

David

[DaveBre]

Hi jindon,

Thanks.

I've attached a sample file with the formulas located in the relevant cells, these cells were originally sitting as 'Check'

Does this help?

The formula need to reference the date in the column in row 1, and the ID in column A.

Thanks

[jindon]

Yes, that's what I needed to see...
Does this work as you want?

                r.FormulaArray = "=IFERROR(INDEX('Leave'!$B:$B,MATCH(1,IF('Leave'!$A:$A=$A" & r.Row & ",IF(" & Cells(1, r.Column).Address(1, 0) & ">=" & _
                                "'Leave'!$I$1:$I$10432,IF(" & Cells(1, r.Column).Address(1, 0) & "<='Leave'!$D:$D,1))),0)),""Check"")"

[DaveBre]

Hi Jindon.

Thankyou very much, that works perfectly. Thanks for taking the time to help.

Cheers

David

[jindon]

You are welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.