Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtotals (97/2003)

    When using the Subtotals option under the Data menu, you get a line with the word "# Total" in column A after each sorted data (see sheet1).
    I would like to have a macro that will replace each "# Total" with the data from the previous row, across a number of columns of my choice. for example: if the parameter is 3, data from columns A to C will be copied to the subtotal row (see sheet2).

    Thank you in advance,
    Avi

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Subtotals (97/2003)

    Does this do what you want?

    Steve

    <pre>Option Explicit
    Sub SubtotalsMacro()
    Dim sStartAdd As String
    sStartAdd = ActiveCell.Address

    With Columns("B:C")
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+R[-1]C"
    .Copy
    .PasteSpecial Paste:=xlValues
    End With
    Application.CutCopyMode = False
    Range("B65536").End(xlUp).Resize(, 2).ClearContents
    Range(sStartAdd).Select
    End Sub</pre>


  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtotals (97/2003)

    Assuming you want to keep "Grand Total", as in Sheet2, use this procedure:

    Sub FillTotals(oCell As Range, lngCols As Long)
    Dim rng As Range
    Dim lngRow As Long
    Set rng = oCell.CurrentRegion
    For lngRow = 1 To rng.Rows.Count - 1
    If Left(rng.Cells(lngRow, rng.Columns.Count).Formula, 9) = "=SUBTOTAL" Then
    rng.Rows(lngRow - 1).Resize(2, lngCols).FillDown
    End If
    Next lngRow
    Set rng = Nothing
    End Sub

    Call it like this, while Sheet1 is the active sheet:

    FillTotals Range("A1"), 3

    The range can be any cell in the table with subtotals.

  4. #4
    New Lounger
    Join Date
    Nov 2001
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals (97/2003)

    Thank you both. I will check your suggestions.
    Avi

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals (97/2003)

    Hi Steve,
    Thank you for your answer. It works ok for columns B and C, but as you can see in sheet2 of my file, I would like also to copy the data in column A to the subtotal row instead of the "# Total" that Excel put there.

    Thanks,
    Avi

  6. #6
    New Lounger
    Join Date
    Nov 2001
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals (97/2003)

    Hi Hans,
    I copied the text of your macro into a module using Visual Basic editor , but when I looked in the Macros list (Alt-F8), it wasn't there. Only when I changed the first line to
    Sub FillTotals()
    I found the Macro FillTotal in the Macros list. When I tried to run it, it failed.
    What am I doing wrong?
    Can you explain in more details how and where should I enter the line to call it?

    Thanks,
    Avi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtotals (97/2003)

    As I indicated in my reply, the code I posted is a procedure with two arguments, hence it is not a macro - macros are procedures without arguments. I also indicated how to call the procedure. If you wish, you can create a macro that calls the procedure with the correct arguments:

    Public Sub EditSubTotals()
    FillTotals Range("A1"), 3
    End Sub

    The reason I used arguments is that it makes the procedure more general, you can use it for different situations.

  8. #8
    New Lounger
    Join Date
    Nov 2001
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotals (97/2003)

    Hi Hans,

    I apologize for asking in a second time, but the idea of a macro that runs a procedure is new for me.
    Thank you very much. Now it is exactly what I needed.

    Avi

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtotals (97/2003)

    No need to apologize! We all have had to learn (and are still learning) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Subtotals (97/2003)

    If you are still looking for an answer for my code add this before the End Sub:

    <pre> With Columns("A:A")
    .Replace What:=" Total", Replacement:="", _
    LookAt:=xlPart, SearchOrder:=xlByColumns, _
    MatchCase:=False
    .Replace What:="Grand", Replacement:="Grand Total", _
    LookAt:=xlPart, SearchOrder:=xlByColumns, _
    MatchCase:=False
    End With</pre>



    Steve

Posting Permissions

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