vba help - to shorten working dictionary code

[Mallesh23]

Hi Team,

I am using below macro works perfectly.
Task was to Make sum of cell values .... exclude strings , #N/A, blank cells..

Is there any alternate way to check below line . just for learning purpose.
Add arr(i, 1), Array(IIf(Application.IsNumber(arr(i, 2)), arr(i, 2), 0), IIf(Application.IsNumber(arr(i, 3)), arr(i, 3), 0), Format(arr(i, 4), "Dd/mm/yyyy"))

if in future Dictionary Items may increase, IIF condition will be lengthy .

Sub test()
Dim arr As Variant
arr = Range("A1").CurrentRegion.Value2

Dim i As Long
Dim dict As New Scripting.Dictionary

With dict

        For i = LBound(arr) + 1 To UBound(arr)
            If Not .Exists(arr(i, 1)) Then
                .Add arr(i, 1), Array(IIf(Application.IsNumber(arr(i, 2)), arr(i, 2), 0), IIf(Application.IsNumber(arr(i, 3)), arr(i, 3), 0), Format(arr(i, 4), "Dd/mm/yyyy"))
            Else
                temp = .Item(arr(i, 1))
                temp(0) = temp(0) + IIf(Application.IsNumber(arr(i, 2)), arr(i, 2), 0)
                temp(1) = temp(1) + IIf(Application.IsNumber(arr(i, 3)), arr(i, 3), 0)
                .Item(arr(i, 1)) = 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)
                c.Offset(, 3).Value = .Item(c.Value)(2)
            End If
        Next c
    End With
End Sub

Below is a table with output.......... In future Columns may increase , how to put 10 columns into dictionary Items,

Book10
	A	B	C	D	E	F	G	H	I
1	Player	Commissioned	Salary Received	Payment Date		Player	Commissioned	Salary Received	Payment Date
2	Sachin	1000	4000	22/10/2020		Sachin	3000	11000	22/10/2020
3	Dhoni	#N/A	5000	23/10/2020		Dhoni	10000	20000	23/10/2020
4	Virat	1000	5000	24/10/2020		Virat	2000	14000	24/10/2020
5	Sachin	1000		25/10/2020
6	Dhoni	5000	7000	26/10/2020
7	Dhoni	5000	8000	26/10/2020
8	Sachin	xxx	6000	26/10/2020
9	Sachin	1000	1000	26/10/2020
10	Virat	1000	9000	26/10/2020
Sheet1

 

[Mallesh23]

Hi Domenic and Rick

Perfect ! it worked, getting output as expected.

Thanks a lot for sharing wonderful code. ? ??


Thanks
mg

 

[Domenic]

@Rick Rothstein

When I run Domenic's code, I get extra information in Columns P, Q and R. Do you not get that as well? Or, perhaps, Mallesh23 is asking for that extra information and I just missed where he did that?

Hi Rick, yes, my original code output all columns. However, in my last post, I amended the code to output only the columns as the OP subsequently specified.

Cheers!

 

[Rick Rothstein]

Hi Rick, yes, my original code output all columns. However, in my last post, I amended the code to output only the columns as the OP subsequently specified.

Ahh, thanks for the clarification.

 

[Domenic]

@Mallesh23 , @Rick Rothstein

Oops, my apologies. It looks like only those select columns were actually required after the OP made the initial change. I just didn't notice it.

Oh well.

Cheers!