3 Attachment(s)
automating calculations and adding to existing table
Hi, Beginner at VBA here. I am trying to automate some reports where the calculations are done and placed into the respective categories. I have a source data sheet:
Attachment 24424
and would like to place it in this template sheet, by consolidating according to the unique type which looks like this:
Attachment 24425
So far, I'm only able to get the types and their totals, but stuck at the count, average, min and max.
By using a dictionary, I would also like to add more values where I could calculate these and put them into the template sheet.
I followed and modified code by Paul Kelly (https://excelmacromastery.com/vba-dictionary/) and was able to produce this output:
Attachment 24426
The code looks something like this:
Code:
Sub ForDictionary_Assign_Sum()
' PART 1 - Read the data
' Get the worksheets
Dim shRead As Worksheet
Set shRead = ThisWorkbook.Worksheets("Sheet1")
' Get the range
Dim rg As Range
Set rg = shRead.Range("B2").CurrentRegion
' Create the dictionary
Dim dict As New Dictionary
' Read through the data
Dim i As Long, types As String
Dim Average As Long
For i = 3 To rg.Rows.Count ' Store the values in a variable
types = rg.Cells(i, 2).Value2
Total = rg.Cells(i, 7).Value2
'Average = Application.WorksheetFunction.Average(.Range(.Cells(i, 7)))
dict(types) = dict(types) + Total
Next i
' PART 2 - Write the data
Dim shWrite As Worksheet
Set shWrite = ThisWorkbook.Worksheets("Sheet3")
With shWrite
' Clear the data in output worksheet
.Cells.ClearContents
' Write header
.Cells(1, 1).Value2 = "type"
.Cells(1, 2).Value2 = "Total"
.Cells(1, 3).Value2 = "Average"
End With
Dim key As Variant, row As Long
row = 2
'Read through each item in the Dictionary
For Each key In dict.Keys
shWrite.Cells(row, 1) = key
shWrite.Cells(row, 2) = dict(key)
shWrite.Cells(row, 3) = Average
row = row + 1
Next key
End Sub
Any help would be appreciated where I could make improvements and learn.
Thank you !
