473,326 Members | 2,127 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Creating a overall data summary by VBA

Hi everyone
I am having a problem that i have to solve

The attached Excel file have a list of purchase made in 10 days
With 3 products A, B, C. And each product have 4 types:
1. 50 (for every product A , B or C that has type < 100)
2. 100 (for every product A , B or C that has type >= 100 and < 175)
3. 200 (for every product A , B or C that has type >= 175 and < 250)
4. 300 (for every product A , B or C that has type >= 100)

The detail is in the Worksheet "Details"
and i want to create a summary in another Worksheet named "Summary"

To make a summary that how many of product of A, B, C (in different Types) are made regardless of the day, as well as the price.

I already attach my Excel file in the zip file. Hope it would help.
I deleted my VBA in there. just to keep it clean

my code is :
(I learn Python before, just the very basic concept , and i am totally new to VBA. so i use what i learned in Python to apply it to VBA, but this code does not work)

And also, my code only work for specific cells, is there any ways to write the code that work widely to the whole sheet, regardless where the data is filled in ?? I really want to know how to do this. Thanks very much

Expand|Select|Wrap|Line Numbers
  1. Sub Counts()
  2.     ActiveCell.Range("C5").Select
  3.     Q_50 = 0
  4.     P_50 = 0
  5.     Q_100 = 0
  6.     P_100 = 0
  7.     Q_200 = 0
  8.     P_200 = 0
  9.     Q_300 = 0
  10.     P_300 = 0
  11.     For Each c In Worksheets("Details").Range("C5:C14").Cells
  12.         If c.Value < 100 Then
  13.             Q_50 = Q_50 + c.Offset(0, -1).Value
  14.             P_50 = P_50 + (c.Offset(0, -1) * c.Offset(0, 2))
  15.             Worksheets("Summary").Range("C3").Value = Q_50
  16.             Worksheets("Summary").Range("E3").Value = P_50
  17.             c.Offset(1, 0).Select
  18.         ElseIf c.Value  >= 100 and c.Value < 175 Then
  19.             Q_100 = Q_100 + c.Offset(0, -1).Value
  20.             P_100 = P_100 + (c.Offset(0, -1) * c.Offset(0, 2))
  21.             Worksheets("Summary").Range("C4").Value = Q_100
  22.             Worksheets("Summary").Range("E4").Value = P_100
  23.             c.Offset(1, 0).Select
  24.        ElseIf c.Value  >= 175 and c.Value < 250 Then
  25.             Q_200 = Q_200 + c.Offset(0, -1).Value
  26.             P_200 = P_200 + (c.Offset(0, -1) * c.Offset(0, 2))
  27.             Worksheets("Summary").Range("C5").Value = Q_200
  28.             Worksheets("Summary").Range("E5").Value = P_200
  29.             c.Offset(1, 0).Select
  30.         Elseif c.Value  >= 250 Then
  31.             Q_300 = Q_300 + c.Offset(0, -1).Value
  32.             P_300 = P_300 + (c.Offset(0, -1) * c.Offset(0, 2))
  33.             Worksheets("Summary").Range("C6").Value = Q_300
  34.             Worksheets("Summary").Range("E6").Value = P_300
  35.             c.Offset(1, 0).Select
  36.             c.Offset(1, 0).Select
  37.         End If
  38.     Next
  39.  
  40. End Sub
Attached Files
File Type: zip Product summary.zip (9.0 KB, 59 views)
Feb 3 '10 #1

✓ answered by Guido Geurs

dear,


I hope this will help you; (see also attachment)

Expand|Select|Wrap|Line Numbers
  1. Sub Calculate_All()
  2. '§ data from sheet 1
  3. Dim A_Q As Integer
  4. Dim A_P As Integer
  5. Dim B_Q As Integer
  6. Dim B_P As Integer
  7. Dim C_Q As Integer
  8. Dim C_P As Integer
  9. '§ Totals
  10. Dim A_Q_50 As Integer
  11. Dim A_TP_50 As Integer
  12. Dim A_Q_100 As Integer
  13. Dim A_TP_100 As Integer
  14. Dim A_Q_200 As Integer
  15. Dim A_TP_200 As Integer
  16. Dim A_Q_300 As Integer
  17. Dim A_TP_300 As Integer
  18. Dim B_Q_50 As Integer
  19. Dim B_TP_50 As Integer
  20. Dim B_Q_100 As Integer
  21. Dim B_TP_100 As Integer
  22. Dim B_Q_200 As Integer
  23. Dim B_TP_200 As Integer
  24. Dim B_Q_300 As Integer
  25. Dim B_TP_300 As Integer
  26. Dim C_Q_50 As Integer
  27. Dim C_TP_50 As Integer
  28. Dim C_Q_100 As Integer
  29. Dim C_TP_100 As Integer
  30. Dim C_Q_200 As Integer
  31. Dim C_TP_200 As Integer
  32. Dim C_Q_300 As Integer
  33. Dim C_TP_300 As Integer
  34. '§ ===== collect data =====
  35. '§ go to first data row
  36.    Worksheets("Details").Select
  37.    Range("a5").Activate
  38.    Do While ActiveCell.Value <> ""
  39. '§ model A
  40.       If ActiveCell.Offset(0, 2).Value <> "" Then
  41.          A_Q = ActiveCell.Offset(0, 1).Value
  42.          A_P = ActiveCell.Offset(0, 3).Value
  43.          Select Case ActiveCell.Offset(0, 2).Value
  44.          Case Is < 100  '§ 50
  45.             A_Q_50 = A_Q_50 + A_Q
  46.             A_TP_50 = A_TP_50 + (A_Q * A_P)
  47.          Case 100 To 174     '§ 100
  48.             A_Q_100 = A_Q_100 + A_Q
  49.             A_TP_100 = A_TP_100 + (A_Q * A_P)
  50.          Case 175 To 249     '§ 200
  51.             A_Q_200 = A_Q_200 + A_Q
  52.             A_TP_200 = A_TP_200 + (A_Q * A_P)
  53.          Case Is > 249     '§ 300
  54.             A_Q_300 = A_Q_300 + A_Q
  55.             A_TP_300 = A_TP_300 + (A_Q * A_P)
  56.          End Select
  57.       End If
  58. '§ model B
  59.       If ActiveCell.Offset(0, 5).Value <> "" Then
  60.          B_Q = ActiveCell.Offset(0, 4).Value
  61.          B_P = ActiveCell.Offset(0, 6).Value
  62.          Select Case ActiveCell.Offset(0, 5).Value
  63.          Case Is < 100  '§ 50
  64.             B_Q_50 = B_Q_50 + B_Q
  65.             B_TP_50 = B_TP_50 + (B_Q * B_P)
  66.          Case 100 To 174     '§ 100
  67.             B_Q_100 = B_Q_100 + B_Q
  68.             B_TP_100 = B_TP_100 + (B_Q * B_P)
  69.          Case 175 To 249     '§ 200
  70.             B_Q_200 = B_Q_200 + B_Q
  71.             B_TP_200 = B_TP_200 + (B_Q * B_P)
  72.          Case Is > 249     '§ 300
  73.             B_Q_300 = B_Q_300 + B_Q
  74.             B_TP_300 = B_TP_300 + (B_Q * B_P)
  75.          End Select
  76.       End If
  77. '§ model C
  78.       If ActiveCell.Offset(0, 8).Value <> "" Then
  79.          C_Q = ActiveCell.Offset(0, 7).Value
  80.          C_P = ActiveCell.Offset(0, 9).Value
  81.          Select Case ActiveCell.Offset(0, 8).Value
  82.          Case Is < 100  '§ 50
  83.             C_Q_50 = C_Q_50 + C_Q
  84.             C_TP_50 = C_TP_50 + (C_Q * C_P)
  85.          Case 100 To 174     '§ 100
  86.             C_Q_100 = C_Q_100 + C_Q
  87.             C_TP_100 = C_TP_100 + (C_Q * C_P)
  88.          Case 175 To 249     '§ 200
  89.             C_Q_200 = C_Q_200 + C_Q
  90.             C_TP_200 = C_TP_200 + (C_Q * C_P)
  91.          Case Is > 249     '§ 300
  92.             C_Q_300 = C_Q_300 + C_Q
  93.             C_TP_300 = C_TP_300 + (C_Q * C_P)
  94.          End Select
  95.       End If
  96.       ActiveCell.Offset(1, 0).Activate
  97.    Loop
  98. '§ ===== dump data =====
  99.    Worksheets("Summary").Select
  100. '§ 50
  101.    Range("c3").Activate
  102.    ActiveCell.Value = A_Q_50
  103.    ActiveCell.Offset(0, 2).Value = A_TP_50
  104.    ActiveCell.Offset(0, 3).Value = B_Q_50
  105.    ActiveCell.Offset(0, 5).Value = B_TP_50
  106.    ActiveCell.Offset(0, 6).Value = C_Q_50
  107.    ActiveCell.Offset(0, 8).Value = C_TP_50
  108. '§ 100
  109.    ActiveCell.Offset(1, 0).Activate
  110.    ActiveCell.Value = A_Q_100
  111.    ActiveCell.Offset(0, 2).Value = A_TP_100
  112.    ActiveCell.Offset(0, 3).Value = B_Q_100
  113.    ActiveCell.Offset(0, 5).Value = B_TP_100
  114.    ActiveCell.Offset(0, 6).Value = C_Q_100
  115.    ActiveCell.Offset(0, 8).Value = C_TP_100
  116. '§ 200
  117.    ActiveCell.Offset(1, 0).Activate
  118.    ActiveCell.Value = A_Q_200
  119.    ActiveCell.Offset(0, 2).Value = A_TP_200
  120.    ActiveCell.Offset(0, 3).Value = B_Q_200
  121.    ActiveCell.Offset(0, 5).Value = B_TP_200
  122.    ActiveCell.Offset(0, 6).Value = C_Q_200
  123.    ActiveCell.Offset(0, 8).Value = C_TP_200
  124. '§ 300
  125.    ActiveCell.Offset(1, 0).Activate
  126.    ActiveCell.Value = A_Q_300
  127.    ActiveCell.Offset(0, 2).Value = A_TP_300
  128.    ActiveCell.Offset(0, 3).Value = B_Q_300
  129.    ActiveCell.Offset(0, 5).Value = B_TP_300
  130.    ActiveCell.Offset(0, 6).Value = C_Q_300
  131.    ActiveCell.Offset(0, 8).Value = C_TP_300
  132. End Sub


br,

5 1774
Guido Geurs
767 Expert 512MB
dear,


I hope this will help you; (see also attachment)

Expand|Select|Wrap|Line Numbers
  1. Sub Calculate_All()
  2. '§ data from sheet 1
  3. Dim A_Q As Integer
  4. Dim A_P As Integer
  5. Dim B_Q As Integer
  6. Dim B_P As Integer
  7. Dim C_Q As Integer
  8. Dim C_P As Integer
  9. '§ Totals
  10. Dim A_Q_50 As Integer
  11. Dim A_TP_50 As Integer
  12. Dim A_Q_100 As Integer
  13. Dim A_TP_100 As Integer
  14. Dim A_Q_200 As Integer
  15. Dim A_TP_200 As Integer
  16. Dim A_Q_300 As Integer
  17. Dim A_TP_300 As Integer
  18. Dim B_Q_50 As Integer
  19. Dim B_TP_50 As Integer
  20. Dim B_Q_100 As Integer
  21. Dim B_TP_100 As Integer
  22. Dim B_Q_200 As Integer
  23. Dim B_TP_200 As Integer
  24. Dim B_Q_300 As Integer
  25. Dim B_TP_300 As Integer
  26. Dim C_Q_50 As Integer
  27. Dim C_TP_50 As Integer
  28. Dim C_Q_100 As Integer
  29. Dim C_TP_100 As Integer
  30. Dim C_Q_200 As Integer
  31. Dim C_TP_200 As Integer
  32. Dim C_Q_300 As Integer
  33. Dim C_TP_300 As Integer
  34. '§ ===== collect data =====
  35. '§ go to first data row
  36.    Worksheets("Details").Select
  37.    Range("a5").Activate
  38.    Do While ActiveCell.Value <> ""
  39. '§ model A
  40.       If ActiveCell.Offset(0, 2).Value <> "" Then
  41.          A_Q = ActiveCell.Offset(0, 1).Value
  42.          A_P = ActiveCell.Offset(0, 3).Value
  43.          Select Case ActiveCell.Offset(0, 2).Value
  44.          Case Is < 100  '§ 50
  45.             A_Q_50 = A_Q_50 + A_Q
  46.             A_TP_50 = A_TP_50 + (A_Q * A_P)
  47.          Case 100 To 174     '§ 100
  48.             A_Q_100 = A_Q_100 + A_Q
  49.             A_TP_100 = A_TP_100 + (A_Q * A_P)
  50.          Case 175 To 249     '§ 200
  51.             A_Q_200 = A_Q_200 + A_Q
  52.             A_TP_200 = A_TP_200 + (A_Q * A_P)
  53.          Case Is > 249     '§ 300
  54.             A_Q_300 = A_Q_300 + A_Q
  55.             A_TP_300 = A_TP_300 + (A_Q * A_P)
  56.          End Select
  57.       End If
  58. '§ model B
  59.       If ActiveCell.Offset(0, 5).Value <> "" Then
  60.          B_Q = ActiveCell.Offset(0, 4).Value
  61.          B_P = ActiveCell.Offset(0, 6).Value
  62.          Select Case ActiveCell.Offset(0, 5).Value
  63.          Case Is < 100  '§ 50
  64.             B_Q_50 = B_Q_50 + B_Q
  65.             B_TP_50 = B_TP_50 + (B_Q * B_P)
  66.          Case 100 To 174     '§ 100
  67.             B_Q_100 = B_Q_100 + B_Q
  68.             B_TP_100 = B_TP_100 + (B_Q * B_P)
  69.          Case 175 To 249     '§ 200
  70.             B_Q_200 = B_Q_200 + B_Q
  71.             B_TP_200 = B_TP_200 + (B_Q * B_P)
  72.          Case Is > 249     '§ 300
  73.             B_Q_300 = B_Q_300 + B_Q
  74.             B_TP_300 = B_TP_300 + (B_Q * B_P)
  75.          End Select
  76.       End If
  77. '§ model C
  78.       If ActiveCell.Offset(0, 8).Value <> "" Then
  79.          C_Q = ActiveCell.Offset(0, 7).Value
  80.          C_P = ActiveCell.Offset(0, 9).Value
  81.          Select Case ActiveCell.Offset(0, 8).Value
  82.          Case Is < 100  '§ 50
  83.             C_Q_50 = C_Q_50 + C_Q
  84.             C_TP_50 = C_TP_50 + (C_Q * C_P)
  85.          Case 100 To 174     '§ 100
  86.             C_Q_100 = C_Q_100 + C_Q
  87.             C_TP_100 = C_TP_100 + (C_Q * C_P)
  88.          Case 175 To 249     '§ 200
  89.             C_Q_200 = C_Q_200 + C_Q
  90.             C_TP_200 = C_TP_200 + (C_Q * C_P)
  91.          Case Is > 249     '§ 300
  92.             C_Q_300 = C_Q_300 + C_Q
  93.             C_TP_300 = C_TP_300 + (C_Q * C_P)
  94.          End Select
  95.       End If
  96.       ActiveCell.Offset(1, 0).Activate
  97.    Loop
  98. '§ ===== dump data =====
  99.    Worksheets("Summary").Select
  100. '§ 50
  101.    Range("c3").Activate
  102.    ActiveCell.Value = A_Q_50
  103.    ActiveCell.Offset(0, 2).Value = A_TP_50
  104.    ActiveCell.Offset(0, 3).Value = B_Q_50
  105.    ActiveCell.Offset(0, 5).Value = B_TP_50
  106.    ActiveCell.Offset(0, 6).Value = C_Q_50
  107.    ActiveCell.Offset(0, 8).Value = C_TP_50
  108. '§ 100
  109.    ActiveCell.Offset(1, 0).Activate
  110.    ActiveCell.Value = A_Q_100
  111.    ActiveCell.Offset(0, 2).Value = A_TP_100
  112.    ActiveCell.Offset(0, 3).Value = B_Q_100
  113.    ActiveCell.Offset(0, 5).Value = B_TP_100
  114.    ActiveCell.Offset(0, 6).Value = C_Q_100
  115.    ActiveCell.Offset(0, 8).Value = C_TP_100
  116. '§ 200
  117.    ActiveCell.Offset(1, 0).Activate
  118.    ActiveCell.Value = A_Q_200
  119.    ActiveCell.Offset(0, 2).Value = A_TP_200
  120.    ActiveCell.Offset(0, 3).Value = B_Q_200
  121.    ActiveCell.Offset(0, 5).Value = B_TP_200
  122.    ActiveCell.Offset(0, 6).Value = C_Q_200
  123.    ActiveCell.Offset(0, 8).Value = C_TP_200
  124. '§ 300
  125.    ActiveCell.Offset(1, 0).Activate
  126.    ActiveCell.Value = A_Q_300
  127.    ActiveCell.Offset(0, 2).Value = A_TP_300
  128.    ActiveCell.Offset(0, 3).Value = B_Q_300
  129.    ActiveCell.Offset(0, 5).Value = B_TP_300
  130.    ActiveCell.Offset(0, 6).Value = C_Q_300
  131.    ActiveCell.Offset(0, 8).Value = C_TP_300
  132. End Sub


br,
Attached Files
File Type: zip Product summary_v1.zip (23.5 KB, 55 views)
Feb 3 '10 #2
@ggeu
Thank you so much!! work like a charm!! i have a quick question for you, hope you can help me. Would it be possible to write a code that work for an updated version (for example, what if suddenly i have product A1, A2, A3 instead of just A). I realize for the code given above, i have to go and change the code manually. Is there anyway to write a code that will accomodate for that ?? thanks in advance!!
Feb 9 '10 #3
Guido Geurs
767 Expert 512MB
Dear,

Do you mean something like this:

Once a workbook with products=
A with subclass A1, A2, A3
B with subclass B1, B2

And an other time something with =

A with subclass A1, A2
B with subclass B1, B2, B3
C with subclass C1

I think it's possible to write a general code whatever the number of products.
Just count the products and calculate the offset for each data.
I will see what I can do if this is what you wants.

br,
Feb 13 '10 #4
yes, that is what i mean. Because there may be different situations where different products have different sub-products. so if the set is different i would have to change the code manually. Hope you can help. Really appreciated.
Feb 13 '10 #5
Guido Geurs
767 Expert 512MB
dear,

Sorry for this late replay but I'm also a newby and have still to learn a lot on the possibilities of Excel and VBA.

What I have in mind:
An Excel with 3 type of sheets:
One with the summary of the products
One with the list of products so I can automate the creation of the sheets of the products.
And the sheets with the products.
These sheets stand for a group of similar products like you explained A with A1, A2, ...
The working and description of the parts are explained in the attached documents.

I hope this is what You are looking for.
Am I on the right track ???

br,
Attached Files
File Type: zip EXCEL creating overall data sum 3.9 DOCS.zip (91.9 KB, 53 views)
Feb 25 '10 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: David Elliott | last post by:
I am creating a configuration class to read / write a standard configuration file. <?xml version="1.0" encoding="utf-8"?> <configuration> <appSettings> <add key="ConnectionString"...
2
by: Fatih BOY | last post by:
Hi, I want to send a report from a windows application to a web page like 'report.asp' Currently i can send it via post method with a context like local=En&Username=fatih&UserId=45&Firm=none...
2
by: Michael.Covington | last post by:
I am trying to create a DTS package using C#. I have done this in VB6 without any problems. However, when I convert everything over to C# I get the 'System.InvalidCastException'. I am running...
0
by: msnews.microsoft.com | last post by:
Hello All, I created a control and it is not showing up in my Web Page? I am new to this. Can somebody please explain what I am doing wrong. Thanks Anand Ganesh Here is my control code....
7
by: Mark Waser | last post by:
Hi all, I'm trying to post multipart/form-data to a web page but seem to have run into a wall. I'm familiar with RFC 1867 and have done this before (with AOLServer and Tcl) but just can't seem...
14
by: Steve Teeples | last post by:
I don't understand why I cannot use a property to modify data within a struct. Can someone tell me why I get the error "Cannot modify the return value of "myData.TheData" because it is not a...
4
by: tshad | last post by:
I am trying to set up an Image authorization where you type in the value that is in a picture to log on to our site. I found a program that is supposed to do it, but it doesn't seem to work. ...
5
by: Vibhesh | last post by:
I am facing problem with TimeSpan structure when DirectX is used. Following is the sample code that causes the problem: ...
0
by: mzwilli | last post by:
Hi, I'm trying to create browsable DataSource and DataMember properties on a user control. The idea is to create a descendent object, place a dataset then specify the DataSource (DataSet) and...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.