CELL with or without .value

[idnoidno]

Sub t4()Dim dic As Object
Dim i As Long
Dim irow  As Long

Worksheets("sheet3").Select
Set dic = CreateObject("scripting.dictionary")
irow = Cells(Rows.Count, 1).End(xlUp).Row
Range("f2:F7").ClearContents

For i = 2 To irow
    dic.Add Cells(i, 1).Value, Cells(i, 2).Value
Next i

For i = 2 To irow
    Cells(i, 6).Value = dic.Item(Cells(i, 5).Value)
Next i

Set dic = Nothing
End Sub

dic.Add Cells(i, 1).Value, Cells(i, 2).Value
Cells(i, 6).Value = dic.Item(Cells(i, 5).Value)

dic.Add Cells(i, 1), Cells(i, 2)
Cells(i, 6) = dic.Item(Cells(i, 5))

With or without Value makes different results,
Cells(i, 1).Value is not equal to Cells(i, 1)?

[p45cal]

Could you supply a spreadsheet with such values? Only need a handful of values which demonstrate the anomaly.

[idnoidno]

2017-06-26_170144.jpg

[p45cal]

That's not a spreadsheet, it's a picture of one.
I can't even begin to get the results you have in column F with your code.

With
dic.Add Cells(i, 1).Value, Cells(i, 2).Value
you're adding keys and items to the dictionary which are values, which I suspect is what you want.

With
dic.Add Cells(i, 1), Cells(i, 2)
you're adding the cell object (range object) to the dictionary, both as item and key. This is because you can do this with dictionaries.

You probably want this combo:
dic.Add Cells(i, 1).Value, Cells(i, 2).Value
Cells(i, 6) = dic.Item(Cells(i, 5).Value)

where the Cells(i, 6) is using the default property of Value! [actually, the default property is the Item object, but never mind!]
All other references without .Value were referring to the cell (range) itself.

Adding the lines:
aaa = dic.keys
bbb = dic.items
somewhere in the code after the dictionary has finished being added to, then examining the contents of aaa and bbb in the Locals pane of the vbe will show you what's in the dictionary.

[idnoidno]

I probably know what you mean, I also know that I may be a VBA beginners, so I have a lot of code do not know, thank you for your explanation, I hope to have the opportunity to further ask you about Dictionary.

[Paul_Hossler]

I suspect you were looking for something like this

Without the value, it's the Range that is in the dictionary, not the value of the cell

Look at the watch window in the screen shot

Option Explicit

Sub t4()
    Dim dic As Object
    Dim i As Long
    Dim irow  As Long
     
    Worksheets("sheet1").Select
    Set dic = CreateObject("scripting.dictionary")
    
    irow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("f2:F7").ClearContents
     
    For i = 2 To irow
        If dic.Exists(Cells(i, 1).Value) Then
            dic(Cells(i, 1).Value) = dic(Cells(i, 1).Value) + Cells(i, 2).Value
        Else
            dic.Add Cells(i, 1).Value, Cells(i, 2).Value
        End If
    Next I
     
    irow = Cells(Rows.Count, 5).End(xlUp).Row
    For i = 2 To irow
        If dic.Exists(Cells(i, 5).Value) Then
           Cells(i, 6).Value = dic.Item(Cells(i, 5).Value)
        End If
    Next I
     
    Set dic = Nothing
End Sub

Capture.JPG

[idnoidno]

According to your explanation, sometimes there are the same with or without Value, but a few cases are not the same,right?

[mdmackillop]

You can output Keys and Items without looping

 Cells(1, 10).Resize(dic.Count) = Application.Transpose(dic.Keys)
    Cells(1, 11).Resize(dic.Count) = Application.Transpose(dic.Items)