Alternate to loop and copy formulas
Results 1 to 4 of 4

Thread: Alternate to loop and copy formulas

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,242
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    3 Thread(s)

    Default Alternate to loop and copy formulas

    Hi,

    I have a header row with a mixture of constants and formulas; using cell formatting to mask formulae (Formula bar visible). This row is 228 columns wide, and has a fixed pattern of 3 columns with formula, 16 columns constant values x 12

    The whole header range is named: Main_Formula

    I use following to copy formula and apply to rows below but it is slow:
    Code:
    Sub Apply_Formulas(byref LR as Long)
    
    Dim r as Range
    Dim rng as Range: set rng = Range("Main_Formula").Specialcells(xlcelltypeFormulas)
    
    With Application
      .ScreenUpdating = False
      .CalculationMode = xlCalculationManual
    End With
    
    For Each r in Rng
      r.Copy
      r.Offset(1).Resize(LR - 1).PasteSpecial xlPasteFormulas
    Next r
    
    With Application
      .CutCopyMode = False
      .ScreenUpdating = False
      .CalculationMode = xlCalculationManual
    End With
    
    Set rng = Nothing
    
    End Sub
    I tried something like:
    [/code]
    With rng
    .Copy
    .Resize(LR).PasteSpecial xlPasteFormulas
    End With
    [/code]
    But this gave run time 1004 error, suggesting I'm not defining the paste range correctly?


    Any suggestions for faster code?

    TIA,
    Jack


  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,655
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Alternate to loop and copy formulas

    You cannot resize non-contiguous ranges. Try
    Code:
    For Each r In Rng
      r.Resize(Lr).FillDown
    Next r
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,242
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Alternate to loop and copy formulas

    Suspected so, thanks for confirming @Fluff and your suggestion is a little faster than what I had.
    Last edited by JackDanIce; Jul 21st, 2019 at 02:46 PM.


  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,655
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Alternate to loop and copy formulas

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •