vba help - Dictionary key issue

[Mallesh23]

Hi Team,

I am printing dictionary keys getting extra keys which are highlighted in Red.

Below are my code.

Option Explicit

Sub TEST()

Dim ar As Variant

ar = Range("a1").CurrentRegion.Offset(1).Value

    Dim dict As New Scripting.Dictionary
    Dim i As Long
    Dim skey As String
    Dim cAmount As Double
    Dim k As String
    Dim tmp As Variant
    

With dict
    
    For i = LBound(ar, 1) To UBound(ar, 1)
        skey = ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3)
        cAmount = IIf(Application.IsNumber(ar(i, 6)), ar(i, 6), 0)
        If Not .Exists(skey) Then
            .Item(skey) = Array(ar(i, 4), ar(i, 5), cAmount)
        Else
            tmp = .Item(skey)
            tmp(2) = tmp(2) + cAmount
            .Item(skey) = tmp
        End If
    Next i
    
    Range("I2").Resize(.Count).Value = WorksheetFunction.Transpose(.Keys)
    Range("J2").Resize(.Count).Value = Application.Index(.Items, 0, 1)
    Range("K2").Resize(.Count).Value = Application.Index(.Items, 0, 2)
    Range("L2").Resize(.Count).Value = Application.Index(.Items, 0, 3)


End With

End Sub

Getting wrong value in Range("I6"). and Range("L6")

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	C1	C2	C3	Invoice	Trn ID	Notional			Unique	Invoice	Trn ID	Notional
2	X	Y	Z	1000	5000	1223			X|Y|Z	1000	5000	11648
3	P	Q	R	2000	6000	#N/A			P|Q|R	2000	6000	5000
4	A	B	C	3000	7000	4632			A|B|C	3000	7000	12552
5	X	Y	Z	200	143	3395			X|Y|N	7000	2000	2606
6	P	Q	R	180	115				||			0
7	A	B	C	124	161	1920
8	X	Y	Z	163	159	3030
9	X	Y	N	7000	2000	2606
10	X	Y	Z	1000	5000	4000
11	P	Q	R	2000	6000	5000
12	A	B	C	3000	7000	6000
Sheet1

Thanks
mg

 

[Peter_SSs]

ar = Range("a1").CurrentRegion.Offset(1).Value

By offsetting A1.CurrentRegion by one row, you are including a blank row at the bottom (row 13 in your example), hence that extra value you don't want

Try loading ar like this instead

With Range("A1").CurrentRegion
  ar = .Offset(1).Resize(.Rows.Count - 1).Value
End With

 

[Mallesh23]

Hi Peter,

Thank you so much it worked ! ?


Thanks
mg