We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Copy and Paste Excel Array Formula

Golfer219
Golfer219 asked
on
Medium Priority
471 Views
Last Modified: 2012-05-11
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

Comment
Watch Question

Author

Commented:
One thing to note - while all cells in B2 through AW2 contain formulas, not all of them contain array formulas.
CERTIFIED EXPERT
Top Expert 2008

Commented:
wks.Range("B2:AW2500").FormulaArray = wks.Range("B2:AW2").Formula

Kevin
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
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

Author

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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.