Pivot Table seems like a good option...
Compare Excel | Excel Templates | DownloaderXL Pro
Finance Templates & Add-In Bundle | NeuroXL Predictor | Construction Estimating
Merge Excel The Easy Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages
Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models In Excel | Excel Web Pages | Excel Video Training
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
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
_______________________________________________
Hi,
Try,
HTHVB: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
Agree. K.I.S.SOriginally Posted by Ger Plante
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
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 at 01:02.
Hi,
Use code tags while posting codes.
change the last part
HTHVB: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
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
Hi,
Change
toVB:.Offset(1).Resize(n, 10).PasteSpecial xlPasteFormats
VB:.Resize(n + 1, 10).PasteSpecial xlPasteFormats
You SHOULD had taken the advice of the other 2 in this Thread and K.I.S.S
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks