I'm trying to replace some values in an Excel worksheet by using a VBA dictionary. When I did this last year, it worked perfectly. Upon trying it again it is adding blank values to duplicate keys, and not writing anything to the cells as a result.
I've tried both early binding and late binding with no success.
Here is the code with late binding.
Sub someDict()
Dim myDict As Object
Set myDict = CreateObject("Scripting.Dictionary")
Dim n As Long
Dim i As Long
myDict.Add "A", "Amaretto Sour"
myDict.Add "B", "Bourbon"
myDict.Add "C", "Cosmopolitan"
myDict.Add "D", "Daiquiri"
myDict.Add "E", "Electric Lemonade"
myDict.Add "F", "Four Horsemen"
myDict.Add "G", "Gin and Tonic"
myDict.Add "H", "Hurricane"
myDict.Add "I", "Irish Coffee"
myDict.Add "J", "John Collins"
Debug.Print "Total: " & myDict.Count()
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, 3) = "TEST" Then
Debug.Print myDict.Count()
Cells(i, 5) = myDict(Cells(i, 4))
Debug.Print myDict.Count()
End If
Next i
Debug.Print "Total after loop: " & myDict.Count()
Dim k As Variant
For Each k In myDict.Keys
' Print key and value
Debug.Print k, myDict(k)
Next
End Sub
Here is the table structure:
The current output in the immediate is:
Total: 10
10
11
11
12
12
13
13
14
14
15
15
16
16
17
Total after loop: 17
A Amaretto Sour
B Bourbon
C Cosmopolitan
D Daiquiri
E Electric Lemonade
F Four Horsemen
G Gin and Tonic
H Hurricane
I Irish Coffee
J John Collins
J
H
G
F
E
C
A
I would've expected it to be:
Total: 10
10
10
10
10
10
10
10
10
10
10
10
10
10
10
Total after loop: 10
A Amaretto Sour
B Bourbon
C Cosmopolitan
D Daiquiri
E Electric Lemonade
F Four Horsemen
G Gin and Tonic
H Hurricane
I Irish Coffee
J John Collins
Can anyone tell me what I am doing wrong (aside from using a VBA dictionary), and further to that, why it has previously worked for me?
Could it be due to an update to scrobj.dll?
Thanks.


debug.print ninterested to know what valuen = Cells(Rows.Count, 1).End(xlUp).Rowis