Excel VBA How to append column of value to array?

Question

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

What is codeArr? You can't match an entire array like that. — findwindow
– findwindow, Commented Jun 16, 2016 at 20:37
codeArr is just the name of my array. Sorry for the confusion — Bubs
– Bubs, Commented Jun 16, 2016 at 20:49
I think this will help you stackoverflow.com/questions/3017852/… — Scott Craner
– Scott Craner, Commented Jun 16, 2016 at 20:54
Thanks Scott, I was looking at this earlier. However, the dimension of that array was fixed. I needed to secure an infinite dimension for different data files, which is what I included ReDim to resize my array everytime I add a string into my array — Bubs
– Bubs, Commented Jun 16, 2016 at 21:00
That is the beauty of using either the dictionary or the collection. It will grow as needed. You can iterate through a variable range and try to add each one, if it is a duplicate it will not be added. If it is unique it will be. In the end you end up with a collection or dictionary of only unique values. You do not need to declare a size to dictionary or collection just keep adding. Then you can pull from them to create your unique list. — Scott Craner
– Scott Craner, Commented Jun 16, 2016 at 21:12

Matt Cremeens · Accepted Answer · 2016-06-17 02:37:21Z

1

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.

edited Jun 17, 2016 at 2:37

answered Jun 16, 2016 at 20:40

Matt Cremeens

5,2118 gold badges42 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Bubs Over a year ago

Thanks Matt, should If IsInArray(cells(i,1).Value) Then be If IsInArray(cells(i,1).Value,arr)? Would you also happen to know why my ReDim Preserve arr(count) is invalid?

Matt Cremeens Over a year ago

I made an edit. Should be IsInArray(cells(i, 1).Value, codeArr). ReDim Preserve ... should work fine. Can we see your array?

Bubs Over a year ago

I corrected my ReDim but at the moment andother error within the function popped up IsInArray = UBound(Filter(arr, stringToBeFound)) > -1 saying "subscription out of range

Matt Cremeens Over a year ago

What is in your array?

Matt Cremeens Over a year ago

And what is in column A?

|

Collectives™ on Stack Overflow

Excel VBA How to append column of value to array?

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related