Adding totals in bands of 10

  • Hi,


    I am having trouble putting together code.


    I have two rows of data in columns A and B. Row A contains a count i.e. 1,2,3.. etc. It goes all the way down to around 13,000 rows. However for this specific task I only need the macro to calculate until row 1003.


    Column A containing the count of data needs to be split into sets of 10, i.e. rows 3-12, 13-22, 23-33 and so on, until row 1003 so the data next to it in column B can be totalled according to the counts.


    I have a feeling it has something to do with subtotals, but I have had no success.


    I have also attached a small sample of the file for a better understanding of what I am trying


    Many thanks in advance for anyones help!


    Vandana

  • Re: Adding totals in bands of 10


    Hi Vandana,


    Try this:


    [vba]
    Sub sumatten()
    Range("A3").Select
    For i = 1 To 100
    ActiveCell.Offset(10, 0).Insert shift:=xlDown
    ActiveCell.Offset(10, 1).FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
    ActiveCell.Offset(11, 0).Select
    Next i
    End Sub
    [/vba]


    This code makes set of 10 rows starting from A3 and puts SUM funtion in Column B to total the 10 rows above.


    Please post back if you have any difficulty. My settings here will not allow me to download the file and hence this code I worked based on your post.


    HTH.

  • Re: Adding totals in bands of 10


    :thanx: for your help!


    It worked successfully! But now I have a new issue.. The data that has been totalled now needs to be placed into a graph, I have managed to get this far...


    Code
    Sub graphdata()
    Range("b13,b24,b35,b46,b57,b68,b79,b90,b101,b112,b123,b134,b145,b156,b167,b178 ETC...
    Selection.Copy
    Range("f27").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    End Sub


    As you can see I want to select the relevant totals and place them elsewhere on the spreadsheet to produce a graph on the totals.


    Is there a way of completing this without having to type in every cell reference? The cells are in column B, begin at B13, and are at at every 11th cell i.e. B13, B24, B35 etc.


    Many thanks!


    Vandana

  • Re: Adding totals in bands of 10


    Hi Vandana,


    Try the following:


    [vba]
    Sub taketotals_sheet2()
    Range("b3").Select
    For i = 27 To 126
    Sheet1.Range("f" & i) = ActiveCell.Offset(10, 0).Value' Change the Sheet reference here to suit your requirements
    ActiveCell.Offset(11, 0).Select
    Next i
    End Sub
    [/vba]


    You can change this reference to your liking.


    HTH.

  • Re: Adding totals in bands of 10


    Hi Kris,


    May be the second part of Vandana's question can be achieved by using formula, but are you saying that one can insert rows using a (first part of the question) formula..interesting!!


    My settings here will not allow me to view your excel book. Glad if you could explain me how...


    Thanx

  • Re: Adding totals in bands of 10


    Hi!


    Thanks for all your suggestions.. All very useful!


    I will certainly keep the formula in mind for any extensions on the work, but the code was sufficient.


    Many thanks again!


    Vandana

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!