CODEWITHSUNDEEP
arraysvbaexcel
Bubs
Bubs

Reputation: 105

Excel VBA How to append column of value to array?

I would like to search through a column of data and compare the values. If a value is not in the array, then I want to append the value to the array. Otherwise, I keep looking through each row. I'm having a little trouble with the syntax. Can I get some help?

When I run this I get an error saying "Invalid Procedure call or argument" on the IsError(Application.Match(cells(i, 4).Value, codeArr, False)) function.

For i = 1 To 17381
  If IsError(Application.Match(cells(i, 1).Value, codeArr, False)) Then

    ReDim Preserve codeArr(count)
    codeArr(count) = cells(i, 1)
    count = count + 1
  End If
Next i

Upvotes: 0

Views: 2039

Answers (1)

Matt Cremeens
Matt Cremeens

Reputation: 5151

Try using this UDF

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = UBound(Filter(arr, stringToBeFound)) > -1
End Function

and then replace 

If IsError(Application.Match(cells(i, 1).Value, codeArr, False)) Then

with

If Not IsInArray(cells(i, 1).Value, codeArr) Then

I believe it'll accomplish what you're after.

EDIT Example input: 

Dim codeArr As Variant
codeArr = Array(4, 5, 6)
cnt = 4 'Use this instead of Count as Count is a reserved word

If Column A had 1,2,3 and 4 in rows 1, 2, 3 and 4, respectively, then codeArr would contain the values (4, 5, 6, 1, 2, 3) if you looped i = 1 to 4.

Upvotes: 1

Related Questions