Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Subtotal Rows & Summary Report Of Dynamic Table

1. I agreed to these rules
Join Date
25th November 2008
Posts
14

## 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.

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

Excel Video Tutorials / Excel Dashboards Reports

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

Pivot Table seems like a good option...

3. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

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

Hi,

Try,

VB:
```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

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

5. I agreed to these rules
Join Date
25th November 2008
Posts
14

## Re: Subtotal Rows & 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:
VB:
```Range("A12").Select
Selection.Copy
Range("C32:F32").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy

```

I am really sorry for bothering you on this.

Thanks

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

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Subtotal Rows & Summary Report Of Dynamic Table

Hi,

Use code tags while posting codes.

change the last part

VB:
```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

7. I agreed to these rules
Join Date
25th November 2008
Posts
14

## Re: Subtotal Rows & 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(%)

Thanks

Raja

Excel Video Tutorials / Excel Dashboards Reports

8. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Subtotal Rows & Summary Report Of Dynamic Table

Hi,

Change

VB:
```.Offset(1).Resize(n, 10).PasteSpecial xlPasteFormats

```
to

VB:
```.Resize(n + 1, 10).PasteSpecial xlPasteFormats

```

9. ## Re: Subtotal Rows & Summary Report Of Dynamic Table

10. I agreed to these rules
Join Date
25th November 2008
Posts
14

## Re: Subtotal Rows & 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.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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