scripting.dictionary doesn't recognise numbers

Question

got this code:

Dim a, i As Long
With Range("K1", Range("K" & Rows.Count).End(xlUp))
    a = .Value
End With
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    .Add "test", 1
    For i = 1 To UBound(a, 1)
        If .exists(a(i, 1)) Then
            Cells(i, 14).Value = 1
        Else
            Cells(i, 14).Value = 0
        End If
    Next
End With

It perfectly will give me a "1" in column 14 when the word "test" is anywhere in column K.

If I change the ".Add "test", 1" line to ".Add "21", 1" it only returns "0" although I have "21" in column K.

On the net is says that .key values can be any value(text, numbers, etc). As column K only contains numbers(1 to 25) I need the script to mark occurrences of 1 of those numbers and I really want to use "scripting.dictionary" as it is the fastest way.

What am I doing wrong or doesn't "scripting.dictionary" support numbers only.

Hi Everyone, solved it. needed to change line to ".Add 21, 1". So "21" without the quotes. — Richard, Jul 15 '12 at 06:21
You should post this as an answer and accept it so others can see that your problem was solved, even if you solved it yourself :) — JimmyPena, Jul 16 '12 at 17:35

score 1 · Answer 1 · edited Apr 03 '14 at 20:25

1

Solved it. Needed to change the line to:

.Add 21, 1

So 21 without the quotes.

edited Apr 03 '14 at 20:25

MrWhite

43,179
8
60
84

answered Jul 18 '12 at 08:31

Richard

91
1
3

scripting.dictionary doesn't recognise numbers

1 Answers1