Long Array in VBA

Hi all,

I'm hoping someone can get me over the line on this little problem that I'm annoyingly close to resolving but having stared at this all day, the answer still alludes me!

I've attempted to split a rather nasty nested if array formula using the above as a guide but keep coming stuck with the classic "run-time error 13 type mismatch" error.

I know the formula works outside of VBA but when my end goal is to take this array formula, copy and paste it into ~318,000 cells, and then copy and paste all of the results as values, you can see why i'm wanting to go the VBA way (also for functionality and performance)

Through macro recording i've been able to develop the code for all the necessary steps outlined above, with the exception of pasting in the array formula due to character length. So i'm hoping someone could at least fix this for me and the rest should (fingers crossed) work.

I've attached a sample workbook with the array formula in cell I4 on the "AIR FULL" sheet. Module30 has my work done to date with the array formula presented in R1C1 format. Module 32 has it presented how it is in Cell I4.

I'll reply with the examples of codes too, the formulae at that long I hit the character limit for threads too!

Any help would be greatly appreciated. Thanks in advance.

Module30

Please Login or Register  to view this content.

Module 32

Please Login or Register  to view this content.

Also just as an FYI, I'm not set in stone with this as a solution. Certainly open to other ideas if anyone else has a smarter way of achieving the same result.

I had investigated the use of 'cases' as many of the nested if arguments are grouped based on the top value in each column. but I was unsure how to apply this to such a large range and I'd still be left in the predicament of the grouped if arguments still exceeding the 255 character limit for VBA array formulae.

You could try temporarily renaming the referenced worksheets to something with a single character and also splitting the formula it into more parts with aliases.

Originally Posted by scottiex

that is a long formula you have there....

Can you describe as simply and logically as possible in words what the formula does?

In a nutshell, it's a 'cleaning' formula. There is an input sheet where the User enters X's in a matrix with the column headings being a hierarchical classification system, and attributes for the row headers. The formula reads the input sheet and identifies any errors where there should be an X and the output is the 'AIR FULL' sheet, which is essentially the corrected input sheet. There are a couple of other sheets within this workbook that then reference that 'AIR FULL' sheet. Here's an example that might hopefully be easier to explain.

Attachment 732307

In the above, the User has put an X in the orange column, indicating they want to know "AreaPerOccupant" for every level of the classification that begins with 'A'. The intent with the input sheet is the user doesn't need to enter X's for all of the sub-levels, the formula does this for them when creating the 'AIR FULL' sheet. However, in this attached example, the user has tried to populate the sub-levels but has done so incorrectly. 'A01 Site', 'A01.04 Site Preparation' and 'A01.04.02 Underpinning' should all have X's in them (which the formula does pick up).

The formula does also look 'up' the hierarchy too which adds a level of complexity. Let's say there wasn't an X in the orange column, but there was an X under 'A01.04.02 Underpinning' and everything else remains the same. The formula would recognise that 'A01.04 Site Preparation' should have an X because all of the green sub-levels have X's and would populate an X accordingly under 'A01.04 Site Preparation' in the 'AIR FULL' sheet.

I had managed to cobble something together that works based on some inspiration from ublud above. I'd split the formula up using the Name Manager, and utilising a helper reference in the top row, created a much simpler nested if formula that would only apply the Named Formula that was applicable to the cell reference. I've then written this to a command button and completed the remaining steps in my original post RE copying and pasting as values, but the whole process is horrendously slow!

If this can be simplified into code then i'm all for it!