MurdochQuill
Board Regular
- Joined
- Nov 21, 2020
- Messages
- 84
- Office Version
- 365
- Platform
- Windows
Hi all,
Trying to average some unique ID's in VBA.
I use an in-cell formula but I would like to make it part of a macro, I have
Here is formula.
=IF(A2="","",AVERAGEIF($A$2:$A$200,A2,$B$2:$B$200))
Column A is the ID, column B is the value. Formula is on row C. EG:
Here is a snippet that can sort the unique ID's, but I'm not sure how to rejig it to give average instead of counting the amount of ID's.
Trying to average some unique ID's in VBA.
I use an in-cell formula but I would like to make it part of a macro, I have
Here is formula.
=IF(A2="","",AVERAGEIF($A$2:$A$200,A2,$B$2:$B$200))
Column A is the ID, column B is the value. Formula is on row C. EG:
ID | Value | Average of Unique ID (Above formula) |
1 | 10 | 13 |
1 | 16 | 13 |
2 | 2 | 2 |
3 | 5 | 11.66 |
3 | 10 | 11.66 |
3 | 20 | 11.66 |
4 | 10 | 10 |
Here is a snippet that can sort the unique ID's, but I'm not sure how to rejig it to give average instead of counting the amount of ID's.
VBA Code:
Sub CountUniqueValues()
Dim LstRw As Long, Rng As Range, List As Object, ListCount As Long
LstRw = Cells(Rows.Count, "B").End(xlUp).Row
Set List = CreateObject("Scripting.Dictionary")
For Each Rng In Range("B6:B" & LstRw)
If Not List.Exists(Rng.Value) Then List.Add Rng.Value, Nothing
Next
ListCount = List.Count
End Sub