-1

Can anyone help me with this macro to create multiple sub totals in one column? Any help would be great. I have a group of numbers in column Y. which begins at row 16. The data is listed on every three lines until the end of that section then there is a gap of around thirty lines then it beings again. I want to create a macro to count how many numbers >45 in each section. Put the total 2 rows below the last data point of each section. In column X on the same row place Number>45

Sub Sample()
    Dim result As Long, firstrow As Long, lastrow As Long
    Dim ws As Worksheet
    Dim rng As Range


    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find Lastrow in Col Y
        lastrow = .Range("Y" & .Rows.Count).End(xlUp).Row
        '~~> Set First row
        firstrow = 16

        '~~> Set your range
        Set rng = .Range("Y" & firstrow & ":Y" & lastrow)

        '~~> Put relevant values
        .Range("x" & lastrow + 2).Value = "Total>45"
        .Range("y" & lastrow + 2).Value = _
        Application.WorksheetFunction.CountIf(rng, ">45")

    End With
End Sub
Community
  • 1
  • 1
user2254486
  • 37
  • 1
  • 1
  • 6

1 Answers1

0

try the below procedure

and loop backwards to ROW=1 like this:

Sub setTotals()
Dim iRow As Integer
Dim iLastRow As Integer
Dim sFormulaTargetAddress As String

iLastRow = ActiveSheet.Range("Y" & ActiveSheet.Rows.Count).End(xlUp).Row


iRow = iLastRow
Do Until iRow = 1
If Range("Y" & iRow).Value <> "" Then
    '
    ' define the section
    sFormulaTargetAddress = "Y" & Range("Y" & iRow).End(xlUp).Row & ":Y" & iRow & ""
    '
    ' Put in the COUNTIF > 45 of the current section...
    '
    Range("Y" & iRow + 2).Formula = "=COUNTIF(" & sFormulaTargetAddress & ","">45"")"
    '        '
    'Range("X" & iRow + 2).Formula = "=COUNTIF(" & sFormulaTargetAddress & ","">45"")"
    Range("X" & iRow + 2).value="Numbers > 45"
    '
    ' find the next section
    iRow = Range("Y" & iRow).End(xlUp).Row
    If Range("Y" & iRow) <> "" Then iRow = Range("Y" & iRow).End(xlUp).Row
Else
    iRow = Range("Y" & iRow).End(xlUp).Row
End If
Loop


End Sub

Screenshots

HTH

Philip

Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148
  • Hi Philip thanks for the help. Still racking my head on his. I am not sure how to set up the count. It has to loop each time, get a total print total and exit each loop. Then begin again – user2254486 Apr 11 '13 at 08:41
  • well, consider how you would do it manually. In Excel you would use CTRL-{Up Key} to move to the next used cell, or next unused cell. So, in VBA, the equivalent of that is `activecell.end(xlup)` - each time you get to a new block, you take the cell 2 rows below, and add a `Cell.formulaR1C1=SUM(...)` – Our Man in Bananas Apr 11 '13 at 08:44
  • try it now, I have added more detail for you to play around with and step through (using F8) – Our Man in Bananas Apr 11 '13 at 08:54
  • 2
    @Philip: I didn't downvote you but I guess the reason for downvote is that you gave an incorrect suggestion. `lastrow = .Range("Y" & .Rows.Count).End(xlUp).Row` is correct. Nothing wrong with that line. `lastrow = .Range("Y65536").End(xlUp).Row` on the other hand should be avoided as you are hardcoding the values. Also a piece of advice... No point abusing in the forum. You will only invite more negative votes :) – Siddharth Rout Apr 11 '13 at 08:55
  • I don't get it, I agree we shouldn't hard code values, however, I have seen 65536 used many a time to get the last row (AHA, Excel 2007+ has 1 million rows)... perhaps I should suggest `activesheet.usedrange.rows.count` (which didn't give a good result) so maybe `Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row` ? – Our Man in Bananas Apr 11 '13 at 09:04
  • I would have thought that someone could give me a comment warning me that I made a dumb mistake rather than downvote ! – Our Man in Bananas Apr 11 '13 at 09:13
  • I didnt vote on anything. So I am lost on whats going on. I welcome the help and input. I am new to this. thanks – user2254486 Apr 11 '13 at 09:17
  • @user2254486: any luck with my latest code procedure? By stepping through the code using F8 whilst looking at the worksheet you should be able to see what's goiong on... – Our Man in Bananas Apr 11 '13 at 09:18
  • I am sorry for any problems that I may have created. I am new to this and I am just trying to solve a problem. I am having a hard time at it. So any help is welcome to solve the problem and stop the pain in my head. lol – user2254486 Apr 11 '13 at 09:24
  • you have not created any problems, don't worry about it, we are glad to help, especially as you are trying to learn and help yourself... – Our Man in Bananas Apr 11 '13 at 09:27
  • @Philip: Regarding your comment below mine.. you might want to see this? http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba – Siddharth Rout Apr 11 '13 at 09:28
  • @Philip: I see that you have deleted your comment. That's good :) Instead of abusing, the best way to handle it is to ask the downvoter (politely) as to why the post was downvoted ;) – Siddharth Rout Apr 11 '13 at 09:29
  • 1
    @SiddharthRout, once again, I find myself doubling up on the +1's both here and on your other answer :) But I've given a couple of options in my code – Our Man in Bananas Apr 11 '13 at 09:30
  • CountIF (range,” ”) You want to count if the range is blank correct Is the range countif(y:irow,” “) – user2254486 Apr 11 '13 at 10:14
  • @user2254486: I have added the COUNTIF in the loop so it will put the count of numbers > 45 in column X – Our Man in Bananas Apr 11 '13 at 10:36
  • @user2254486 - any feedback on the most recent code above which includes COUNTIF > 45 ? – Our Man in Bananas Apr 11 '13 at 11:15
  • Philip I tried the code its not working. It adds each row of data point not the the section of data points. So if I had three section of 10 data points the result should be 3 totals. What I am getting is 30 totals. Can you help – user2254486 Apr 11 '13 at 11:22
  • I think you need to show us an example of your worksheet/data in a picture. – Our Man in Bananas Apr 11 '13 at 11:33
  • I have added pictures showing what happens in my test of the code. – Our Man in Bananas Apr 11 '13 at 11:43
  • 8:55 9:26 0:30 9:30 0:34 35 9:03 10:02 0:58 10:15 1:11 71 6:55 7:21 0:26 7:27 0:32 33 Total > 45 1 8:55 9:42 0:47 9:47 0:51 52 8:55 9:26 0:30 9:33 0:37 38 6:50 7:21 0:31 7:23 0:33 33 8:56 NT Cant Cal NT 0 0 Total > 45 1 7:20 7:56 0:36 NT 0 0 7:20 7:56 0:36 NT 0 0 7:20 7:49 0:29 7:53 1 0:33 33 7:20 7:49 0:29 7:53 1 0:33 33 Total > 45 0 – user2254486 Apr 11 '13 at 11:53
  • 9:03 10:02 0:58 10:15 1:11 71 6:55 7:21 0:26 7:27 0:32 33 Total > 45 1 8:55 9:42 0:47 9:47 0:51 52 8:55 9:26 0:30 9:33 0:37 38 6:50 7:21 0:31 7:23 0:33 33 8:56 NT Cant Cal NT 0 0 Total > 45 1 – user2254486 Apr 11 '13 at 11:59
  • I am trying to upload a pic – user2254486 Apr 11 '13 at 12:01
  • Please can you take a screen print of your MS Excel, paste it into a new BMP file in the MS Paint application, save it (on your hard disc), Edit your question, and use the Add Picture icon to upload the screenshot so we can see what you are looking at – Our Man in Bananas Apr 11 '13 at 12:04
  • Almost there. Using the left picture. Column x row 9 should say Numbers > Column y row 9 should have the number 2 That the first section Column x row 17 should say Numbers > Column y row 17 should have the number 1 That’s the second section etc – user2254486 Apr 11 '13 at 12:08
  • so you want to count the individual sections, rather than SUM the numbers? – Our Man in Bananas Apr 11 '13 at 12:10
  • In each section count the numbers >45 Then move to the next section and count the numbers >45. Each section will have its own total showing the total counts over 45 for each section. I hope this helps. – user2254486 Apr 11 '13 at 12:16
  • 1
    @user2254486 - try it now, I have modified my code so it should do what you want now, and if it does, plase **Accept it** – Our Man in Bananas Apr 12 '13 at 11:01
  • is it the Answer you needed? If so, please **Accept it** by *clicking the check mark* – Our Man in Bananas Apr 18 '13 at 08:15