Change all regular formulas into array formulas

**sans** · 06-10-2012, 12:55 PM

Hello,

I have a big number of cells I would like to convert into array formulas. Currently, I have to go to one by one and C+S+E. Is it possible to select a range and for the macro to automatically convert all the formulas in the selected range into array formulas?

Thank you for your time

**JosephP** · 06-10-2012, 02:31 PM

do you mean something like this

Please Login or Register  to view this content.

**sans** · 06-10-2012, 02:42 PM

Hi Jospeh, thank you for the reply. I get a "Compile error: Invalid Next control variable reference error". Thank you

**TMS** · 06-10-2012, 03:10 PM

Next rcell or just Next

**JosephP** · 06-10-2012, 03:27 PM

I apologize-it was a typo and should be 'next rcell' (I prefer not to use Next on its own)

**sans** · 06-10-2012, 03:33 PM

No worries. However I still receive an error, '1004': Unable to set the FormulaArray property of the Range class. Thank you

**JosephP** · 06-10-2012, 03:47 PM

how long is the formula that it fails on? there is a limit of 255 characters for the formulaarray property

**sans** · 06-11-2012, 06:39 AM

Yes, this should be the problem. In all sheets the min. characters are 350 and in other sheets the characters are around 1540. Is there any possible way to overcome the limit or to C+S+E all cells? Thank you very much

**JosephP** · 06-11-2012, 06:49 AM

yeah-you need to use a placeholder and then replace it. see this ddoe post: http://www.dailydoseofexcel.com/arch...rmulas-in-vba/
I reckon your workbook is probably going to be slower than molasses going uphill in January though. ;-)

**TMS** · 06-11-2012, 07:07 AM

I prefer not to use Next on its own

I tend to use just Next but with the variable commented out. I read somewhere once, a long time ago, that it was more efficient and/or unnecessary ... and I've been doing it ever since.

Hence: Next 'rcell

Whatever, I don't imagine it's a big deal

TMS

**sans** · 06-11-2012, 07:23 AM

Thank you for the replies. Could you please explain how I can use the code in the link for this purpose? The formulas in one sheet are 1540 characters and eaxh formula uses a different range and value. Is it possible to use the code in the link for this purpose? Thank you

**JosephP** · 06-11-2012, 07:44 AM

if all the formulas are completely different, then you will have to create a formula parser and it's probably quicker to change them by hand frankly. if they are basically the same but with different ranges and comparison values, then you can probably figure out a pattern to replace.

**TMS** · 06-11-2012, 07:59 AM

We've not seen any of your formulae or data structure. I'm guessing they're not all different. Can't you just manually convert the top row and then autofill down?

Regards, TMS

**sans** · 06-11-2012, 10:26 AM

Thank you for your replies. This is the formula I am using in one sheet (credits to NBVC for both formulas)

Please Login or Register  to view this content.

In another sheet I use the following formula

Please Login or Register  to view this content.

Both formulas address around 2000 different ranges in each sheet. So I will need to C+S+E 4000 times and drag down. If it would be possible to automate the c+s+e process it would save me a few days

Thank you

**JosephP** · 06-11-2012, 10:51 AM

it's way too early for me to even think about following that and I don't think your workbook will ever finish calculating if you have 4000 of those copied down lots of rows.

**TMS** · 06-11-2012, 12:11 PM

Can we see a sample of the workbook please. I'm struggling to understand the setup.

Regards, TMS

**sans** · 06-11-2012, 03:20 PM

I am attaching a small example. The real data go down to around 4000 thousands rows.

You can see the formulas in AF42, AG42,AH42 ... The formulas continue for hundreds of columns, each one formula is using different ranges. If I could change all formulas in AF42, AG42,AH42 and so on, I could then drag them down to 4000 rows.

Thank you very much for your help

**NBVC** · 06-18-2012, 11:28 AM

You should probably be asking for someone to help you to perform the same task using VBA instead of using the formula. The formula wasn't meant to be copied down and/or across so many rows/columns... it will grind the workbook to a halt. You need a VBA alternate solution to perform the task and loop through the columns and rows...to return results...