Weighted Average with Criteria Using VBA

Squat · Mar 22nd 2017

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.

I already used PivotTables and today I do this calculation through formulas (sumproduct), both take a long time to calculate, because the data file is very large (from 400,000 to 600,000 lines), the computer screen arrives To be dimmed.

For example:
The weighted average for product "101" of store "A" in Jan / 17 is:

(each quantity * each unit price) / quantity sum

((13 * 6) + (12 * 5)) / (13 + 12) which will be equal to 5.52

I'm trying to do for each product per store per month / year.

And do for each product with all the stores together per month / year.

My intention is to automate in VBA because the time gain would be better. And since I'm starting in VBA, it would be a learning experience for me.

Sorry for my english, I'm using google translator.

Follow the files for better understanding.

forum.ozgrid.com/index.php?attachment/71912/forum.ozgrid.com/index.php?attachment/71913/

Thank you for your attention.

bryce · Mar 22nd 2017

Re: Weighted Average with Criteria Using VBA

How about this. The code sits in the Data file and you will need to have your Report file open also

Code

Sub CalculateStuff()
    Dim wbData As Workbook, wbSummary As Workbook
    Dim wsData As Worksheet, ws As Worksheet
    Dim rowData As Long, z As Long
    Dim x As Integer, y As Integer
    Dim product As Variant
    Dim store As String
    Dim dte As Date
    Dim sumProd As Double, sumUnits As Double, totalStore As Double, totalStoreUnits As Double
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set wbData = ThisWorkbook
    Set wbSummary = Workbooks("Report")
    
    Set wsData = wbData.Sheets("Data")
    
    rowData = wsData.Range("A" & Rows.Count).End(xlUp).Row
    
    For Each ws In wbSummary.Sheets
        product = ws.Range("B1")
    
        For x = 4 To 15 ' Dates on each summary sheet
            For y = 2 To 6 'Stores in row 3
            
                store = ws.Cells(3, y)
                dte = ws.Cells(x, 1)
                
                For z = 2 To rowData
                    If wsData.Range("A" & z) = dte And wsData.Range("B" & z) = store And wsData.Range("C" & z) = product Then
                        sumProd = sumProd + wsData.Range("E" & z) * wsData.Range("D" & z)
                        sumUnits = sumUnits + wsData.Range("D" & z)
                        
                        totalStore = totalStore + wsData.Range("E" & z) * wsData.Range("D" & z)
                        totalStoreUnits = totalStoreUnits + wsData.Range("D" & z)
                    End If
                Next z
                
                If sumUnits > 0 Then
                    ws.Cells(x, y) = sumProd / sumUnits
                Else
                    ws.Cells(x, y) = 0
                End If
                sumProd = 0#
                sumUnits = 0#
            Next y
            
            If totalStoreUnits > 0 Then
                ws.Cells(x, 7) = totalStore / totalStoreUnits
            Else
                ws.Cells(x, 7) = 0
            End If
            
            totalStore = 0#
            totalStoreUnits = 0#
        Next x
    Next ws
                    
    Application.Calculation = xlCalculationAutomatic
    
End Sub

Display More

Squat · Mar 22nd 2017

Re: Weighted Average with Criteria Using VBA

Hi Bryce,

Very perfect.

I just have to thank you, for me, that I am a beginner in VBA was an great learning.

I took the liberty of adjusting the lines: ws.Cells (x, y) = 0 for ws.Cells (x, y) = "-" and ws.Cells (x, 7) = 0 for ws.Cells (x, 7) = "-".

Sorry for my english, I use google translator.

Thank you one more time!

bryce · Mar 22nd 2017

Re: Weighted Average with Criteria Using VBA

Hey Squat. I am glad it worked for you and no worries on your English! To get the dash "-" instead of 0.00 use formatting. Select your tables and click the comma button on the Home tab. PS I think most of us are still apprentices...myself included:) Happy coding friend!

Squat · Mar 22nd 2017

Re: Weighted Average with Criteria Using VBA

Hi Bryce,

Thanks again.

I'll mark the thread as solved.

Health and peace for you.

Weighted Average with Criteria Using VBA

Participate now!

Share