• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

Copy and Paste Excel Array Formula

I'm working on a piece of code for a tool that transposes the formulas in row 2 through row 2500.  While I could actually copy and paste the formula (thus taking up more memory), I was hoping to do so via the code below.  The issue I'm running into is that certain array formulas in a cell in row 2 lose the array when pasted (i.e. the { } are removed).

Is it possible to transpose these formulas without actually copying and pasting the formulas?

Thanks for your help!
'VB Code
wks.Range("B2:AW2500").Formula = wks.Range("B2:AW2").Formula



'Example of array formula in row 2
={SUM(IF(INDIRECT(Variables!$O$3)=$A2,INDIRECT(Variables!$O$17)))}

Open in new window

0
Golfer219
Asked:
Golfer219
1 Solution
 
Golfer219Author Commented:
One thing to note - while all cells in B2 through AW2 contain formulas, not all of them contain array formulas.
0
 
zorvek (Kevin Jones)ConsultantCommented:
wks.Range("B2:AW2500").FormulaArray = wks.Range("B2:AW2").Formula

Kevin
0
 
Rory ArchibaldCommented:
I don't see how that is transposing? If you just want to fill, then:
wks.Range("B2:AW2500").FillDown

Open in new window

0
 
balatheexpertCommented:
one thing, by populating the formulae with the help of code, it might save your time, but i dont see any chance of saving memory space occupied...,
anyways, the method zorvek has told should work!!!

-Bala
0
 
Golfer219Author Commented:
Thanks Rorya - This worked perfect - the reason why the FormulaArray code didn't work is that not all formulas were arrays, only about half of them.  This code worked extremely fast too.  Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now