CODEWITHSUNDEEP
excelvba
Transitioned
Transitioned

Reputation: 17

How to compare an array string with a range value

I've pulled out unique person's names in a dictionary and then put them into an array:

'Extract the dictionary's keys as a 1D array
    Dim result As Variant
    result = dic.Keys

That works fine - I can see the unique names in the array called result - Jane, Bob, etc. with: MsgBox Join(result, vbCrLf)

Its using a loop variable called valCounter2 so I should be able to loop through the array with result(valCounter2)

Now I'm trying to look for those names in column CX:

    Dim LSearchRow As Integer
        LSearchRow = 2
        While Len(Range("A" & CStr(LSearchRow)).Value) > 0
            If Range("CX" & CStr(LSearchRow)).Value = result(valCounter2) Then

The last line is the problem. If I set that line to a real value then it works. So this works: If Range("CX" & CStr(LSearchRow)).Value = "Jane Smith" Then

How do I get it to use the array variable?

Upvotes: 0

Views: 371

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

You need to iterate between array elements, or using Match to check if the string exists. Please, try the next code:

Sub CheckArrayElements()
  Dim LSearchRow As Long, mtch As Variant
        'result should be take from somewhere else...
        LSearchRow = 2
        Do While Len(Range("A" & LSearchRow).Value) > 0
            mtch = Application.match(Range("CX" & LSearchRow).Value, result, 0)
            If IsNumeric(mtch) Then 'the string has been found
                'do here what you need
            End If
            LSearchRow = LSearchRow + 1
        Loop
End Sub

The above sub only shows you how to proceed with an array, in order to do the comparison.

If the array comes from a Dictionary and you do not want only learning about arrays, it is much simpler to follow the suggestion you received in the first comment.

Upvotes: 0

Related Questions