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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

Gos-C, I'm not trying to awkward but you are going to have a model with over 10000 Volatile arrays with each array covering 40000 cells... so that's well a lot of cells and performance impact could be horrendous ... IMHO I think you should look at using a helper column on Sheet1 such that you can dispose of need for arrays in the first instance ... ie store the "index" values for *matches* in another column and rid yourself of need for SMALL array which although more elegant is not going to be efficient (as is often the case -- less elegant = more efficient)

It seems to me from looking at your array formula in S that the only complexity here is the fact that you're looking to return a ROW number where K range = O1 and Y range = S1 ... so to avoid array I would advocate you create a flag in another column, eg: in Z2:Z10051 such that:

Z2:=N($Z1)+(AND($K2=$O$1,$Y2=S$1))

copy that down to Z10051
this will create a step -- with each step you indicate a new match -- you can then use a bog standard match in S in your INDEX to return appropriate value from G... eg (using INDEX/MATCH):

S2: =IF(Sheet2!$AP$5="",IF(ROWS($A$2:$A2)>T$1,"",INDEX($G$2:$G$10051,MATCH(ROWS($A$2:$A2),$Z$2:$Z$10051,0),1)),"")

Far less oppressive in terms of performance... you could probably even switch to LOOKUP, eg:

S2: =IF(Sheet2!$AP$5="",IF(ROWS($A$2:$A2)>T$1,"",LOOKUP(ROWS($A$2:$A2),$Z$2:$Z$10051,$G$2:$G$10051)),"")

I'm not saying this resolves your problem but the route you're going down promises to prove expensive in terms of performance.

Hi DonkeyOte,

Thank you very much for your advise. I will review it later (I am at work) to see if it would work for what I am trying to achieve - please see the following link:

http://www.excelforum.com/excel-misc...own-lists.html

Regards,
Gos-C

Re DonkeyOte’s code in Post 2 of This Thread

I switched to R1C1 Cell Reference Style but the cells references in the formula in S2 remained absolute when copied down. I need them to be mixed - so that DonkeyOte's would work. Any help?

Thanks,
Gos-C

Are you still going down the route of the array in S ?

I will look at this tomorrow as it's getting late over here in the UK... sorry to be a bore but avoid the arrays if you can... ;-)

Before implementing anything below have a read through in full...

Having spent a while rebuilding the formulae for S it dawned on me we were being a little foolish... so an alternative approach presented at the end which is far and away the best option...

re: alteration of formulae in S2:S10051 on sheet1

I did come up with this but even using the philosophy of helper cells in Z (to dispose of array) where:

Z2: =N($Z1)+AND($K2=$O$1,$Y2=$S$1)

it's still painfully slow given it's inserting over 10000 formulae

Please Login or Register  to view this content.

re: we're being dense, no ?

Why not simply store the value from APx in a cell on Sheet1 as you do Hx in O1 ?
The formulae in S thus remain constant ... if we were to store Sheet2!APx to Sheet1!AA1 the formula in S2 becomes simply:

S2: =IF($AA$1="",IF(ROWS($A$2:$A2)>$T$1,"",INDEX($G$2:$G$10051,MATCH(ROWS($A$2:$A2),$Z$2:$Z$10051,0),1)),"")
copied down to S2:S10051 ... ie always looks to AA1 for it's blank test ... and thus never needs to be rewritten... all you need do is adapt the change event to copy both Hx to O1 and APx to AA1, eg:

Please Login or Register  to view this content.

Needless to say -- a squillion squllion times faster... why we didn't think of this before is beyond me... oh well.

Hi DonkeyOte,

I will try your suggestions later and let you know the results.

Thanks a lot for your help - greatly appreciated.

Regards,
Gos-C

Hi everyone,

I have attached a sample of my workbook so you can see what I have so far and, hopefully help me clean it up and working as it should.

Password = test

Some columns are hidden.

I need all the codes (included the ones commented) to work together.

In the code, can you please replace cellule with cell - I think it is french for cell.

Thank you,
Gos-C

Gos-C, I think you need to narrow down and clarify what you want to achieve in phases - I doubt anyone here will have the time to go through your entire file, unprotect, unhide, read through the code & try to work out what it is that you're trying to achieve.

If you post up another file please do so unprotected, unhidden etc etc with clear outline of what you need resolved.

Okay, I will do that later today as I am rushing out to work now.

Thanks for your advice,
Gos-C

Hi everyone,

The following portion of the code in the Patient Database sheet module does not work where it is located but the other portions work as is. However, when I put it at the beginning it works but the other portions do not.

Please Login or Register  to view this content.

Can you please help me fix it so that everything works.

Thank you,
Gos-C

Hi all,

I am at my "whit's end" with this, and I desperately need one-on-one help with this workbook from someone in Toronto. Please PM so that we can make arrangement.

Thank you,
Gos-C