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 - Sub Counts()
-
ActiveCell.Range("C5").Select
-
Q_50 = 0
-
P_50 = 0
-
Q_100 = 0
-
P_100 = 0
-
Q_200 = 0
-
P_200 = 0
-
Q_300 = 0
-
P_300 = 0
-
For Each c In Worksheets("Details").Range("C5:C14").Cells
-
If c.Value < 100 Then
-
Q_50 = Q_50 + c.Offset(0, -1).Value
-
P_50 = P_50 + (c.Offset(0, -1) * c.Offset(0, 2))
-
Worksheets("Summary").Range("C3").Value = Q_50
-
Worksheets("Summary").Range("E3").Value = P_50
-
c.Offset(1, 0).Select
-
ElseIf c.Value >= 100 and c.Value < 175 Then
-
Q_100 = Q_100 + c.Offset(0, -1).Value
-
P_100 = P_100 + (c.Offset(0, -1) * c.Offset(0, 2))
-
Worksheets("Summary").Range("C4").Value = Q_100
-
Worksheets("Summary").Range("E4").Value = P_100
-
c.Offset(1, 0).Select
-
ElseIf c.Value >= 175 and c.Value < 250 Then
-
Q_200 = Q_200 + c.Offset(0, -1).Value
-
P_200 = P_200 + (c.Offset(0, -1) * c.Offset(0, 2))
-
Worksheets("Summary").Range("C5").Value = Q_200
-
Worksheets("Summary").Range("E5").Value = P_200
-
c.Offset(1, 0).Select
-
Elseif c.Value >= 250 Then
-
Q_300 = Q_300 + c.Offset(0, -1).Value
-
P_300 = P_300 + (c.Offset(0, -1) * c.Offset(0, 2))
-
Worksheets("Summary").Range("C6").Value = Q_300
-
Worksheets("Summary").Range("E6").Value = P_300
-
c.Offset(1, 0).Select
-
c.Offset(1, 0).Select
-
End If
-
Next
-
-
End Sub
dear,
I hope this will help you; (see also attachment) - Sub Calculate_All()
-
'§ data from sheet 1
-
Dim A_Q As Integer
-
Dim A_P As Integer
-
Dim B_Q As Integer
-
Dim B_P As Integer
-
Dim C_Q As Integer
-
Dim C_P As Integer
-
'§ Totals
-
Dim A_Q_50 As Integer
-
Dim A_TP_50 As Integer
-
Dim A_Q_100 As Integer
-
Dim A_TP_100 As Integer
-
Dim A_Q_200 As Integer
-
Dim A_TP_200 As Integer
-
Dim A_Q_300 As Integer
-
Dim A_TP_300 As Integer
-
Dim B_Q_50 As Integer
-
Dim B_TP_50 As Integer
-
Dim B_Q_100 As Integer
-
Dim B_TP_100 As Integer
-
Dim B_Q_200 As Integer
-
Dim B_TP_200 As Integer
-
Dim B_Q_300 As Integer
-
Dim B_TP_300 As Integer
-
Dim C_Q_50 As Integer
-
Dim C_TP_50 As Integer
-
Dim C_Q_100 As Integer
-
Dim C_TP_100 As Integer
-
Dim C_Q_200 As Integer
-
Dim C_TP_200 As Integer
-
Dim C_Q_300 As Integer
-
Dim C_TP_300 As Integer
-
'§ ===== collect data =====
-
'§ go to first data row
-
Worksheets("Details").Select
-
Range("a5").Activate
-
Do While ActiveCell.Value <> ""
-
'§ model A
-
If ActiveCell.Offset(0, 2).Value <> "" Then
-
A_Q = ActiveCell.Offset(0, 1).Value
-
A_P = ActiveCell.Offset(0, 3).Value
-
Select Case ActiveCell.Offset(0, 2).Value
-
Case Is < 100 '§ 50
-
A_Q_50 = A_Q_50 + A_Q
-
A_TP_50 = A_TP_50 + (A_Q * A_P)
-
Case 100 To 174 '§ 100
-
A_Q_100 = A_Q_100 + A_Q
-
A_TP_100 = A_TP_100 + (A_Q * A_P)
-
Case 175 To 249 '§ 200
-
A_Q_200 = A_Q_200 + A_Q
-
A_TP_200 = A_TP_200 + (A_Q * A_P)
-
Case Is > 249 '§ 300
-
A_Q_300 = A_Q_300 + A_Q
-
A_TP_300 = A_TP_300 + (A_Q * A_P)
-
End Select
-
End If
-
'§ model B
-
If ActiveCell.Offset(0, 5).Value <> "" Then
-
B_Q = ActiveCell.Offset(0, 4).Value
-
B_P = ActiveCell.Offset(0, 6).Value
-
Select Case ActiveCell.Offset(0, 5).Value
-
Case Is < 100 '§ 50
-
B_Q_50 = B_Q_50 + B_Q
-
B_TP_50 = B_TP_50 + (B_Q * B_P)
-
Case 100 To 174 '§ 100
-
B_Q_100 = B_Q_100 + B_Q
-
B_TP_100 = B_TP_100 + (B_Q * B_P)
-
Case 175 To 249 '§ 200
-
B_Q_200 = B_Q_200 + B_Q
-
B_TP_200 = B_TP_200 + (B_Q * B_P)
-
Case Is > 249 '§ 300
-
B_Q_300 = B_Q_300 + B_Q
-
B_TP_300 = B_TP_300 + (B_Q * B_P)
-
End Select
-
End If
-
'§ model C
-
If ActiveCell.Offset(0, 8).Value <> "" Then
-
C_Q = ActiveCell.Offset(0, 7).Value
-
C_P = ActiveCell.Offset(0, 9).Value
-
Select Case ActiveCell.Offset(0, 8).Value
-
Case Is < 100 '§ 50
-
C_Q_50 = C_Q_50 + C_Q
-
C_TP_50 = C_TP_50 + (C_Q * C_P)
-
Case 100 To 174 '§ 100
-
C_Q_100 = C_Q_100 + C_Q
-
C_TP_100 = C_TP_100 + (C_Q * C_P)
-
Case 175 To 249 '§ 200
-
C_Q_200 = C_Q_200 + C_Q
-
C_TP_200 = C_TP_200 + (C_Q * C_P)
-
Case Is > 249 '§ 300
-
C_Q_300 = C_Q_300 + C_Q
-
C_TP_300 = C_TP_300 + (C_Q * C_P)
-
End Select
-
End If
-
ActiveCell.Offset(1, 0).Activate
-
Loop
-
'§ ===== dump data =====
-
Worksheets("Summary").Select
-
'§ 50
-
Range("c3").Activate
-
ActiveCell.Value = A_Q_50
-
ActiveCell.Offset(0, 2).Value = A_TP_50
-
ActiveCell.Offset(0, 3).Value = B_Q_50
-
ActiveCell.Offset(0, 5).Value = B_TP_50
-
ActiveCell.Offset(0, 6).Value = C_Q_50
-
ActiveCell.Offset(0, 8).Value = C_TP_50
-
'§ 100
-
ActiveCell.Offset(1, 0).Activate
-
ActiveCell.Value = A_Q_100
-
ActiveCell.Offset(0, 2).Value = A_TP_100
-
ActiveCell.Offset(0, 3).Value = B_Q_100
-
ActiveCell.Offset(0, 5).Value = B_TP_100
-
ActiveCell.Offset(0, 6).Value = C_Q_100
-
ActiveCell.Offset(0, 8).Value = C_TP_100
-
'§ 200
-
ActiveCell.Offset(1, 0).Activate
-
ActiveCell.Value = A_Q_200
-
ActiveCell.Offset(0, 2).Value = A_TP_200
-
ActiveCell.Offset(0, 3).Value = B_Q_200
-
ActiveCell.Offset(0, 5).Value = B_TP_200
-
ActiveCell.Offset(0, 6).Value = C_Q_200
-
ActiveCell.Offset(0, 8).Value = C_TP_200
-
'§ 300
-
ActiveCell.Offset(1, 0).Activate
-
ActiveCell.Value = A_Q_300
-
ActiveCell.Offset(0, 2).Value = A_TP_300
-
ActiveCell.Offset(0, 3).Value = B_Q_300
-
ActiveCell.Offset(0, 5).Value = B_TP_300
-
ActiveCell.Offset(0, 6).Value = C_Q_300
-
ActiveCell.Offset(0, 8).Value = C_TP_300
-
End Sub
br,
5 1774
dear,
I hope this will help you; (see also attachment) - Sub Calculate_All()
-
'§ data from sheet 1
-
Dim A_Q As Integer
-
Dim A_P As Integer
-
Dim B_Q As Integer
-
Dim B_P As Integer
-
Dim C_Q As Integer
-
Dim C_P As Integer
-
'§ Totals
-
Dim A_Q_50 As Integer
-
Dim A_TP_50 As Integer
-
Dim A_Q_100 As Integer
-
Dim A_TP_100 As Integer
-
Dim A_Q_200 As Integer
-
Dim A_TP_200 As Integer
-
Dim A_Q_300 As Integer
-
Dim A_TP_300 As Integer
-
Dim B_Q_50 As Integer
-
Dim B_TP_50 As Integer
-
Dim B_Q_100 As Integer
-
Dim B_TP_100 As Integer
-
Dim B_Q_200 As Integer
-
Dim B_TP_200 As Integer
-
Dim B_Q_300 As Integer
-
Dim B_TP_300 As Integer
-
Dim C_Q_50 As Integer
-
Dim C_TP_50 As Integer
-
Dim C_Q_100 As Integer
-
Dim C_TP_100 As Integer
-
Dim C_Q_200 As Integer
-
Dim C_TP_200 As Integer
-
Dim C_Q_300 As Integer
-
Dim C_TP_300 As Integer
-
'§ ===== collect data =====
-
'§ go to first data row
-
Worksheets("Details").Select
-
Range("a5").Activate
-
Do While ActiveCell.Value <> ""
-
'§ model A
-
If ActiveCell.Offset(0, 2).Value <> "" Then
-
A_Q = ActiveCell.Offset(0, 1).Value
-
A_P = ActiveCell.Offset(0, 3).Value
-
Select Case ActiveCell.Offset(0, 2).Value
-
Case Is < 100 '§ 50
-
A_Q_50 = A_Q_50 + A_Q
-
A_TP_50 = A_TP_50 + (A_Q * A_P)
-
Case 100 To 174 '§ 100
-
A_Q_100 = A_Q_100 + A_Q
-
A_TP_100 = A_TP_100 + (A_Q * A_P)
-
Case 175 To 249 '§ 200
-
A_Q_200 = A_Q_200 + A_Q
-
A_TP_200 = A_TP_200 + (A_Q * A_P)
-
Case Is > 249 '§ 300
-
A_Q_300 = A_Q_300 + A_Q
-
A_TP_300 = A_TP_300 + (A_Q * A_P)
-
End Select
-
End If
-
'§ model B
-
If ActiveCell.Offset(0, 5).Value <> "" Then
-
B_Q = ActiveCell.Offset(0, 4).Value
-
B_P = ActiveCell.Offset(0, 6).Value
-
Select Case ActiveCell.Offset(0, 5).Value
-
Case Is < 100 '§ 50
-
B_Q_50 = B_Q_50 + B_Q
-
B_TP_50 = B_TP_50 + (B_Q * B_P)
-
Case 100 To 174 '§ 100
-
B_Q_100 = B_Q_100 + B_Q
-
B_TP_100 = B_TP_100 + (B_Q * B_P)
-
Case 175 To 249 '§ 200
-
B_Q_200 = B_Q_200 + B_Q
-
B_TP_200 = B_TP_200 + (B_Q * B_P)
-
Case Is > 249 '§ 300
-
B_Q_300 = B_Q_300 + B_Q
-
B_TP_300 = B_TP_300 + (B_Q * B_P)
-
End Select
-
End If
-
'§ model C
-
If ActiveCell.Offset(0, 8).Value <> "" Then
-
C_Q = ActiveCell.Offset(0, 7).Value
-
C_P = ActiveCell.Offset(0, 9).Value
-
Select Case ActiveCell.Offset(0, 8).Value
-
Case Is < 100 '§ 50
-
C_Q_50 = C_Q_50 + C_Q
-
C_TP_50 = C_TP_50 + (C_Q * C_P)
-
Case 100 To 174 '§ 100
-
C_Q_100 = C_Q_100 + C_Q
-
C_TP_100 = C_TP_100 + (C_Q * C_P)
-
Case 175 To 249 '§ 200
-
C_Q_200 = C_Q_200 + C_Q
-
C_TP_200 = C_TP_200 + (C_Q * C_P)
-
Case Is > 249 '§ 300
-
C_Q_300 = C_Q_300 + C_Q
-
C_TP_300 = C_TP_300 + (C_Q * C_P)
-
End Select
-
End If
-
ActiveCell.Offset(1, 0).Activate
-
Loop
-
'§ ===== dump data =====
-
Worksheets("Summary").Select
-
'§ 50
-
Range("c3").Activate
-
ActiveCell.Value = A_Q_50
-
ActiveCell.Offset(0, 2).Value = A_TP_50
-
ActiveCell.Offset(0, 3).Value = B_Q_50
-
ActiveCell.Offset(0, 5).Value = B_TP_50
-
ActiveCell.Offset(0, 6).Value = C_Q_50
-
ActiveCell.Offset(0, 8).Value = C_TP_50
-
'§ 100
-
ActiveCell.Offset(1, 0).Activate
-
ActiveCell.Value = A_Q_100
-
ActiveCell.Offset(0, 2).Value = A_TP_100
-
ActiveCell.Offset(0, 3).Value = B_Q_100
-
ActiveCell.Offset(0, 5).Value = B_TP_100
-
ActiveCell.Offset(0, 6).Value = C_Q_100
-
ActiveCell.Offset(0, 8).Value = C_TP_100
-
'§ 200
-
ActiveCell.Offset(1, 0).Activate
-
ActiveCell.Value = A_Q_200
-
ActiveCell.Offset(0, 2).Value = A_TP_200
-
ActiveCell.Offset(0, 3).Value = B_Q_200
-
ActiveCell.Offset(0, 5).Value = B_TP_200
-
ActiveCell.Offset(0, 6).Value = C_Q_200
-
ActiveCell.Offset(0, 8).Value = C_TP_200
-
'§ 300
-
ActiveCell.Offset(1, 0).Activate
-
ActiveCell.Value = A_Q_300
-
ActiveCell.Offset(0, 2).Value = A_TP_300
-
ActiveCell.Offset(0, 3).Value = B_Q_300
-
ActiveCell.Offset(0, 5).Value = B_TP_300
-
ActiveCell.Offset(0, 6).Value = C_Q_300
-
ActiveCell.Offset(0, 8).Value = C_TP_300
-
End Sub
br,
@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!!
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,
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.
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,
Sign in to post your reply or Sign up for a free account.
Similar topics
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"...
|
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...
|
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...
|
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....
|
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...
|
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...
|
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.
...
|
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:
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |