Weighted Average with Criteria Using VBA

S

Squat

New Member
Joined
Mar 21, 2017
Messages
3
Hi,

I'm trying to calculate the weighted average with some criteria (period, store and product) of the Data file and for each average of each product of each store in a certain period, I wanted to paste it into another Workbook (Report) in the worksheets of the respective products.
The problem is that the code I am writing, is not calculating based on these criteria (returns 0.00), if someone can tell me where I am wrong (I am a beginner in VBA), I am grateful.
Detail, I do this via formula, the problem is that they are large files (600,000 lines) and have been too time consuming.
Follow the data for better understanding.
Sorry, I do not speak English, and I'm using google translator.
Thank you for your attention.

PeriodoStoreProductAmountUnit PriceAmount x Unit PriceAll Stores
jan/17A101136,0078,00All
jan/17A102143,0042,00All
jan/17A102153,0045,00All
jan/17B103164,0064,00All
jan/17C102123,0036,00All
jan/17E103105,0050,00All
jan/17E105154,0060,00All
jan/17A104126,0072,00All
jan/17D104143,0042,00All
jan/17A105135,0065,00All
jan/17B105122,0024,00All
jan/17C101144,0056,00All
jan/17C105135,0065,00All
jan/17B101126,0072,00All
jan/17B101154,0060,00All
jan/17A104133,0039,00All
jan/17E105163,0048,00All
jan/17D102135,0065,00All
jan/17C102146,0084,00All
jan/17E101133,0039,00All
jan/17A101125,0060,00All
jan/17B105124,0048,00All
jan/17C104133,0039,00All
jan/17C103125,0060,00All
jan/17A102132,0026,00All
jan/17D104123,0036,00All
jan/17E101142,0028,00All
fev/17B101153,0045,00All
fev/17C105123,0036,00All
fev/17C101143,0042,00All
fev/17B101154,0060,00All
fev/17B104123,0036,00All
fev/17A105143,0042,00All
fev/17E102155,0075,00All
fev/17D102126,0072,00All
fev/17C101144,0056,00All
fev/17E101155,0075,00All
fev/17A101126,0072,00All
fev/17B105144,0056,00All
fev/17C101144,0056,00All
fev/17C101135,0065,00All
fev/17A104126,0072,00All
fev/17D105124,0048,00All
fev/17E102133,0039,00All
fev/17B102123,0036,00All
fev/17C101135,0065,00All
fev/17C101126,0072,00All
fev/17A103154,0060,00All
fev/17D102122,0024,00All

<tbody>
</tbody>

<tbody>
</tbody>

My code:
Private Sub CommandButton1_Click()
Call MedPond
End Sub
--------------------------------------------
Sub MedPond()
Application.ScreenUpdating = False
Dim Rng As Range
Dim Loja As String
Dim Prod As Integer
Dim Periodo As Date
Dim Quant As Integer
Dim Punit As Integer
Dim Q As Double
Dim P As Double
Dim PMP As Integer


Workbooks.Open ("C:\Users\Desktop\Relatorio.xlsx")




If Loja = "A" And Prod = "101" And Periodo = "01/01/2017" Then
Q = Rng.Cells(2, Quant)
P = Rng.Cells(2, Punit)
PMP = (Q * P) / Q
End If


Workbooks("Relatorio.xlsx").Worksheets("101").Cells(4, 2) = PMP


If Loja = "A" And Prod = "101" And Periodo = "01/02/2017" Then
Q = Rng.Cells(2, Quant)
P = Rng.Cells(2, Punit)
PMP = (Q * P) / Q
End If


Workbooks("Relatorio.xlsx").Worksheets("101").Cells(5, 2) = PMP


ActiveWorkbook.Save
ActiveWorkbook.Close


MsgBox "Concluído!"


Application.ScreenUpdating = True
End Sub


I intend to:
Calculate the weighted average for each product in each store each month;
Calculate the weighted average of each product in each month for all stores together;
Paste the result into the worksheet of each product.

In B4 it was to come to 5.52
In B5 it was to come the value of 6.00

Worlsheets Product 101

Store AStore BStore CStore DStore EAll Stores
jan/170,00
feb/170,00
mar/17
apr/17
mai/17
jun/17
jul/17
ago/17
sep/17
oct/17
nov/17
dec/17

<tbody>
</tbody>


I intend do for worksheets Product 102,Product 103,Product 104 and Product 105

Thank you for your attention.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Welcome to MrExcel,

Have you considered using a PivotTable?

If you have Excel 2010 or later, then PowerPivot would be even better due to the large number of rows of data.
 
Upvote 0
Hi Jerry,

Sorry for the delay in answering, my internet is in trouble.

I use excel 2007, I calculate through formula somarproducto, happens that it is very slow, because, there are almost 500 worksheets (one per product) in the Report folder, and in each worksheet are almost a thousand cells with this formula.
They are data of almost 15 years of work, I will divide by year to see if you stay fast.


Thank you for your attention.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
help me with using bar code on existing excel
Replies
1
Views
151
Fluff
Fluff
E
  • Solved
Formula Help: INDEX vs SUMIFS vs VLOOKUP
Replies
4
Views
668
jtakw
jtakw
S
  • Question
VBA: Combining 4 colums into combinations, but skip double values
2
Replies
14
Views
2K
Styx
S
W
  • Solved
How many rows between each data point in a column
Replies
3
Views
426
Fluff
Fluff
H
  • Solved
Copy the first line and paste it at the start of all subsequent sheets in the same tab
Replies
4
Views
190
harzer
H

Forum statistics

Threads
1,216,049
Messages
6,128,496
Members
449,455
Latest member
jesski

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top