1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code copying formula down a column jumping predetermined rows

Discussion in 'VBA Macros' started by Inun, Apr 24, 2018.

  1. Inun

    Inun New Member

    Messages:
    14
    Please help with VBA excel code .I have formula in excel sheet in cell F6 want to copy same formula down that same F column after every 14th row the last formula will be inserted in in cell F2533 .Thanks for your help in advance .


    ▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

  2. Stevie

    Stevie Member

    Messages:
    76
    Hi Inun,
    I have written some code to do this, but it seems starting at 6 and going every 14 rows (6,20,34...etc) ends at 2526
    or going every 13 rows (6,19,32...etc) ends at 2528.

    Please clarify what you want as 2533-6 is not a multiple of 14.

    Either way, here is the code if you wish to modify it for your use.

    Code (vb):

    Sub copyFormula()
        Application.Calculation = xlManual
        Dim ws As Worksheet
        Set ws = Sheets("Sheet1")
        Dim form1 As String
        Dim row As Double
        form1 = ws.Range("F6").formula
        For row = 6 To 2533 Step 13
            ws.Cells(row, 6).Formula = form1
        Next
        Application.Calculation = xlAutomatic
    End Sub
     
    If this was helpful, please click 'Like'!
    Inun likes this.
  3. p45cal

    p45cal Well-Known Member

    Messages:
    1,081
    If, in Stevie's code, you replace both instances of .formula with .formular1c1 the F6 formula's absolute/relative references will be respected.
    Inun likes this.
  4. Inun

    Inun New Member

    Messages:
    14
    Hi Stevie
    Sorry late response the steps is indeed 13 but the last cell where the formula needs to be inserted is 2533
    I pasted the code and am getting run time error 9 saying subscript out of range
    Thanks for a quick response

    The other thing my sheet is sheet38 but the tab called FCW .Do I need to write FWC or sheet38 in in this -Set ws = Sheets("Sheet1")
  5. Inun

    Inun New Member

    Messages:
    14
    Hi

    Thanks for your response
    I have tried that getting the same error message
    run time error 9 saying subscript out of range
    Thanks for a quick response
    How do I get round this ?
  6. Stevie

    Stevie Member

    Messages:
    76
    Hi Inun,
    I still don't understand which cells need the formula and I would imagine the error is to do with something about your sheet which I don't know about.
    Please upload your workbook as per the forum suggested best practice.

    Stevie
    Inun likes this.
  7. Marc L

    Marc L Excel Ninja

    Messages:
    3,793
    Without a complete explanation of the need neither an attachment
    (as per forum rules), just activate the Macro recorder and operate
    manually at least for a couple of destination cells then post here
    your generated code using the appropriate code icon …
    Inun likes this.
  8. Inun

    Inun New Member

    Messages:
    14
    will try manual and if this do not works I may send the attachment ( sensitive info will see if this will be allowed )
    Thanks Marc
  9. Inun

    Inun New Member

    Messages:
    14
    The formula is in F6 and that needs going into F20 , F34 up to F2533
    Thanks Stevie
  10. Marc L

    Marc L Excel Ninja

    Messages:
    3,793
    No sensitive data in any attachment !

    Better is you just first operate manually.
    As if it can't work manually so it can't neither work by code …
  11. Inun

    Inun New Member

    Messages:
    14
    Ok was just worried comp records trying the manual coding and will let you know
    thanks
  12. p45cal

    p45cal Well-Known Member

    Messages:
    1,081
    Code (vb):
    Sub copyFormula()
        Application.Calculation = xlManual
        Dim ws As Worksheet
        Set ws = ActiveSheet 'Sheets("Sheet1") 'this change means it works on whichever sheet is the active sheet.
       Dim form1 As String
        Dim row As Long
        form1 = ws.Range("F6").Formula
        For row = 6 To 2533 Step 14
            ws.Cells(row, 6).Formula = form1
        Next
        'add the last formula (because the last cell with formula added will be on row 2526):
       ws.Cells(2533, 6).Formula = form1
     
        Application.Calculation = xlAutomatic
    End Sub
     
    Inun likes this.
  13. Inun

    Inun New Member

    Messages:
    14
    P45 cal
    This is running the code and looks magic but strange enough the code is not putting the right answers to F20 , F34 etc don't know why this is a very good starting point and much appreciated thanks
  14. Stevie

    Stevie Member

    Messages:
    76
    Hi Inun,
    collating your responses and making some guesses, I am going to assume that cell F2533 doesn't actually contain the formula, it's just the end of the range you are specifying.
    Also, adding P45cal's R1C1 suggestion, try this:
    Code (vb):

    Sub copyFormula()
       Application.Calculation = xlManual
       Dim ws As Worksheet
       Set ws = Sheets("FCW")
       Dim form1 AsString
       Dim row AsDouble
       form1 = ws.Range("F6").formulaR1C1
       For row = 6 To 2533 Step 14
            ws.Cells(row, 6).FormulaR1C1 = form1
       Next
        Application.Calculation = xlAutomatic
    End Sub
     
    If this helped, please click 'Like'!
    Inun likes this.
  15. Stevie

    Stevie Member

    Messages:
    76
    This is likely due to not using R1C1 as P45cal previously suggested. try my response above.
    Inun likes this.
  16. Inun

    Inun New Member

    Messages:
    14
  17. p45cal

    p45cal Well-Known Member

    Messages:
    1,081
    Again,
    Inun likes this.
  18. Inun

    Inun New Member

    Messages:
    14
    F2533 actually contain the last formula
    your code is bringing error and P45cal's is running the code and working but giving a difference in the totals I had before and this is what I am investigating thanks
  19. Inun

    Inun New Member

    Messages:
    14
    yes the absolute and relative referencing was the issue with the numbers

    I suspect I may have more spaces or less between where the formulas need to go so after every 14 may not be constant to get round this can we modify the code to insert the formula where the row is in the line with where it says "direct works" down the column -again the last cell where the formula will go is F2533 thanks
  20. Inun

    Inun New Member

    Messages:
    14
    p45cal has done the magic that is brilliant the formula worked perfectly I had more space on 2 cases that is the reason why my total was different initially
    many thanks
  21. Inun

    Inun New Member

    Messages:
    14
    I want to say thank you to Stevie , p45cal , Marc L and all members in this forum who have taken time to read and respond to this request
    That was a quick turnaround and it has made my life easier
  22. p45cal

    p45cal Well-Known Member

    Messages:
    1,081
    Q1. In which column might we find "direct works"?
    Q2. Is "direct works" the only thing in the cell?
  23. Inun

    Inun New Member

    Messages:
    14
    Hi
    direct works is in C6 and it is the only thing in the cell
    thanks

    the other thing I appear to face all the time I run any code the excel spreadsheets are crashing and freezing and not responding .This has happened with other codes i have used in the past and is happening now again with the code you have just helped me with .What could be causing this ?
  24. p45cal

    p45cal Well-Known Member

    Messages:
    1,081
    I'll come up with something later…


    With a formula like:

    =SUM(SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C,'Subcontractors Lia OnsiteMcA'!D:D,'FWC - Programme'!A:A),IF(SUMIFS('Raw Data'!O:O,'Raw Data'!AQ:AQ,"External construction")>SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C),(SUMIFS('Raw Data'!O:O,'Raw Data'!AQ:AQ,"External construction")-SUMIFS('Subcontractors Lia OnsiteMcA'!Q:Q,'Subcontractors Lia OnsiteMcA'!S:S,'FWC - Programme'!C:C))*A7,0),(SUMIF('Subcontractors Lia OnsiteMcA'!D:D,"Various",'Subcontractors Lia OnsiteMcA'!Q:Q)*A7))

    where there are many references to entire columns (red highlighting), this means that for each column 1 million+ cells are being evaluated, which takes time. Try to reduce them to the likes of $A$1:$A$1000, and $AQ$1:$AQ$1000, where you change the 1000 to the maximum number of rows ever likely to exist on the corresponding sheets (but bear in mind that all ranges referred to within a SUMIFS function should be the same size).
  25. Inun

    Inun New Member

    Messages:
    14
    Ok will narrow the range then and make sure columns same size in SUMIFS thanks

Share This Page