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

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

Perhaps

Please Login or Register  to view this content.

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

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

Please Login or Register  to view this content.

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

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

Please Login or Register  to view this content.

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

OOps, missed this.

Originally Posted by DaveBre

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...

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

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

Please Login or Register  to view this content.

Hi Jindon.

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

Cheers

David

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.