Copy and Paste Excel Array Formula

Posted on 2011-04-26
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

Open in new window

Question by:Golfer219

    Author Comment

    One thing to note - while all cells in B2 through AW2 contain formulas, not all of them contain array formulas.
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    wks.Range("B2:AW2500").FormulaArray = wks.Range("B2:AW2").Formula

    LVL 85

    Accepted Solution

    I don't see how that is transposing? If you just want to fill, then:

    Open in new window

    LVL 2

    Expert Comment

    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!!!


    Author Closing Comment

    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

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now