Announcement

Collapse
No announcement yet.

Subtotal Rows & Summary Report Of Dynamic Table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Subtotal Rows & Summary Report Of Dynamic Table

    Hello

    I am new to Excel VBA and I am having a tough time writing a VBA to convert a set of data in sheet 1 to one in sheet 2(I am enclosing that as a Excel document"Test -Original").I have described what needs to be done (step by step ) below.

    Could you please help ?Any input will be greatly appreciated

    Thanks

    Raja

    Develop a macro
    1. I have a report from SAP BW, the original format of which is in sheet 1.I need to develop a macro using VBA and need the report with the format in sheet 2.

    2. I need a “Results “row after every Bill to Party in column A as below(screen shot 1.doc):



    P.S: The number of customers is dynamic i.e.it keeps changing every month

    4. Nothing needs to be done to column.SAP BW will not overwrite the format and the data in column G.Hence leave it as it is.

    5. Calculate the number of Sales document numbers for each customer and put the value of 1 for every value. If it is blank it should not be counted and put the value of 0 for those rows. (Shown below) Display the sum of the number of sales document numbers in the results row for column H

    6. In the column I, put the value of 1 if the difference column (column G) is 0 and put the value of 0 if the value in the difference column (column G) is any value apart from 0.now sum the value in the results row for each customer and display the summation value in the results row under column I
    7. Compute the percentage which is the values in (column I/Column J)*100 .This should be done only for the results row
    Attached Files

  • #2
    Re: For Inserting A Blank Row And Performing Calculations

    Pivot Table seems like a good option...

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

    Comment


    • #3
      Re: For Inserting A Blank Row And Performing Calculations

      Hi,

      Try,

      Sub kTest()
      Dim a, i As Long, n As Long, w(), CntA As Long, BP, c As Long
      Dim SumSD As Long, cDiff As Long, Per As Single

      CntA = Application.WorksheetFunction.CountA(Range("a13:a" & _
      Range("c" & Rows.Count).End(xlUp).Row))
      a = Range("a13:f" & Range("c" & Rows.Count).End(xlUp).Row)

      ReDim w(1 To UBound(a, 1) + CntA, 1 To 10)

      For i = 1 To UBound(a, 1)
      n = n + 1
      If i = 1 Then
      BP = a(i, 1)
      For c = 1 To 6: w(n, c) = a(i, c): Next
      If a(i, 5) = "#" Then w(n, 7) = 0 Else: w(n, 7) = CDate(a(i, 6)) - CDate(a(i, 5))
      If Trim(a(i, 3)) <> "" Then w(n, 8) = 1 Else: w(n, 8) = 0
      If w(n, 7) = 0 Then w(n, 9) = 1 Else: w(n, 9) = 0
      SumSD = SumSD + w(n, 8): cDiff = cDiff + w(n, 9)
      ElseIf IsEmpty(a(i, 1)) Then
      For c = 1 To 6: w(n, c) = a(i, c): Next
      If a(i, 5) = "#" Then w(n, 7) = 0 Else: w(n, 7) = CDate(a(i, 6)) - CDate(a(i, 5))
      If Trim(a(i, 3)) <> "" Then w(n, 8) = 1 Else: w(n, 8) = 0
      If w(n, 7) = 0 Then w(n, 9) = 1 Else: w(n, 9) = 0
      SumSD = SumSD + w(n, 8): cDiff = cDiff + w(n, 9)
      ElseIf Not IsEmpty(a(i, 1)) Then
      w(n, 2) = "Results"
      w(n, 8) = SumSD: w(n, 9) = cDiff: w(n, 10) = (cDiff / SumSD) * 100
      SumSD = 0: cDiff = 0
      BP = a(i, 1): n = n + 1
      For c = 1 To 6: w(n, c) = a(i, c): Next
      If a(i, 5) = "#" Then w(n, 7) = 0 Else: w(n, 7) = CDate(a(i, 6)) - CDate(a(i, 5))
      If Trim(a(i, 3)) <> "" Then w(n, 8) = 1 Else: w(n, 8) = 0
      If w(n, 7) = 0 Then w(n, 9) = 1 Else: w(n, 9) = 0
      SumSD = SumSD + w(n, 8): cDiff = cDiff + w(n, 9)
      End If
      Next
      n = n + 1
      w(n, 2) = "Results"
      w(n, 8) = SumSD: w(n, 9) = cDiff: w(n, 10) = (cDiff / SumSD) * 100
      With Range("a12")
      .Offset(, 6).Resize(, 4).Value = Array("Difference", "Count(Sales Doc)", "Count(Diff)", "Pecentage(%)")
      .Offset(1).Resize(n, 10).Value = w
      End With
      End Sub


      HTH
      Kris

      ExcelFox

      Comment


      • #4
        Re: For Inserting A Blank Row And Performing Calculations

        Originally posted by Ger Plante
        Pivot Table seems like a good option...
        Agree. K.I.S.S

        Comment


        • #5
          Re: Subtotal Rows &amp; Summary Report Of Dynamic Table

          Hi Kris,

          Thanks a lot for the help.The VBA code really works!!!You are a Star.Thanks once again.

          Just a quick question.The new columns need to have the same format as the old ones i.e.as we use format painter in Excel to copy the format from the old columns and rows.Is that possible?What I did was to record a macro to do this for one cell.The code that is gave me was below:
          Range("A12").Select
          Selection.Copy
          Range("C32:F32").Select
          Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
          SkipBlanks:=False, Transpose:=False
          Application.CutCopyMode = False
          Selection.Copy



          But I need the formatting to be for dynamic rows and columns.Could you please help?.

          I am really sorry for bothering you on this.

          Thanks

          Raja
          Last edited by Krishnakumar; December 3rd, 2008, 01:02.

          Comment


          • #6
            Re: Subtotal Rows &amp; Summary Report Of Dynamic Table

            Hi,

            Use code tags while posting codes.

            change the last part

            With Range("a12")
            .Offset(, 6).Resize(, 4).Value = Array("Difference", "Count(Sales Doc)", "Count(Diff)", "Pecentage(%)")
            .Offset(1).Resize(n, 10).Value = w
            .Offset(1).Rows.Copy
            .Offset(1).Resize(n, 10).PasteSpecial xlPasteFormats
            .Offset(1, 4).Resize(n, 2).NumberFormat = "dd/mm/yyyy"
            Application.CutCopyMode = 0
            End With


            HTH
            Kris

            ExcelFox

            Comment


            • #7
              Re: Subtotal Rows &amp; Summary Report Of Dynamic Table

              Hi Kris,

              Thanks a lot for the help.Sorry to be a pain once again.

              The following headings do not appear to have the same format as the rest of the data.

              Difference Count(Sales Doc) Count(Diff) Pecentage(%)


              Could you please help?

              Thanks


              Raja

              Comment


              • #8
                Re: Subtotal Rows &amp; Summary Report Of Dynamic Table

                Hi,

                Change

                .Offset(1).Resize(n, 10).PasteSpecial xlPasteFormats


                to

                .Resize(n + 1, 10).PasteSpecial xlPasteFormats
                Kris

                ExcelFox

                Comment


                • #9
                  Re: Subtotal Rows &amp; Summary Report Of Dynamic Table

                  You SHOULD had taken the advice of the other 2 in this Thread and K.I.S.S

                  Comment


                  • #10
                    Re: Subtotal Rows &amp; Summary Report Of Dynamic Table

                    Hi Kris,

                    Thanks a lot for the help and sorry for the delay.Apologies....


                    Thanks

                    Raja

                    Dave:The SAP BW that I am using and for which I need the macros, does not recognise the Pivot table hence could not take the advice from K.I.S.S.Thanks anyways.

                    Comment

                    Working...
                    X