PDA

View Full Version : CELL with or without .value



idnoidno
06-25-2017, 11:27 PM
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
06-26-2017, 01:39 AM
Could you supply a spreadsheet with such values? Only need a handful of values which demonstrate the anomaly.

idnoidno
06-26-2017, 02:02 AM
19603

p45cal
06-26-2017, 04:24 AM
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
06-26-2017, 06:19 AM
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
06-26-2017, 08:20 AM
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



19604

idnoidno
06-26-2017, 05:17 PM
According to your explanation, sometimes there are the same with or without Value, but a few cases are not the same,right?

mdmackillop
06-27-2017, 05:54 AM
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)