Hi Team,
While summing data in dictionary, sometimes I come across with string/Blank/#N/A value in column.
how to exclude such type of cells values.
Sub SumUsing_Dictionary_ExcludeBlank_String
Below is a table with expected values are in H and I Columns.
Thanks
mg
While summing data in dictionary, sometimes I come across with string/Blank/#N/A value in column.
how to exclude such type of cells values.
Sub SumUsing_Dictionary_ExcludeBlank_String
VBA Code:
()
Dim arr As Variant
arr = Range("A1").CurrentRegion.Value2
Dim temp As Variant
Dim dict As New Scripting.Dictionary
Dim i As Long
With dict
For i = LBound(arr) To UBound(arr)
If Not .Exists(arr(i, 2)) Then
.Add arr(i, 2), Array(arr(i, 3), arr(i, 4))
Else
temp = .Item(arr(i, 2))
temp(0) = temp(0) + .Item(arr(i, 3))
temp(1) = temp(1) + .Item(arr(i, 4))
.Item(arr(i, 2)) = Temp
End If
Next i
Dim c As Range
For Each c In Range("K2:K4")
If .Exists(c.Value) Then
c.Offset(, 1).Value = .Item(c.Value)(0)
c.Offset(, 2).Value = .Item(c.Value)(1)
End If
Next c
End With
End Sub
Below is a table with expected values are in H and I Columns.
Book17 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Date | Player | Commissioned | Salary Received | Player | Commissioned | Salary Received | ||||
2 | 22/10/2020 | Sachin | 1000 | 4000 | Sachin | 3000 | 15000 | ||||
3 | 23/10/2020 | Dhoni | 1000 | 5000 | Dhoni | 2000 | 20000 | ||||
4 | 24/10/2020 | Virat | 1000 | xxxx | Virat | 2000 | 9000 | ||||
5 | 25/10/2020 | Sachin | 1000 | 4000 | |||||||
6 | 26/10/2020 | Dhoni | 1000 | 7000 | |||||||
7 | 26/10/2020 | Dhoni | #N/A | 8000 | |||||||
8 | 26/10/2020 | Sachin | 1000 | 6000 | |||||||
9 | 26/10/2020 | Sachin | 1000 | ||||||||
10 | 26/10/2020 | Virat | 1000 | 9000 | |||||||
Sheet1 |
Thanks
mg