0

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
admix
  • 1,752
  • 3
  • 22
  • 27
Bubs
  • 85
  • 1
  • 1
  • 11
  • What is `codeArr`? You can't match an entire array like that. – findwindow Jun 16 '16 at 20:37
  • codeArr is just the name of my array. Sorry for the confusion – Bubs Jun 16 '16 at 20:49
  • 1
    I think this will help you http://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array – Scott Craner Jun 16 '16 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 Jun 16 '16 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 Jun 16 '16 at 21:12
  • How is `codeArr` initialised? If it is un-initialised you will get this error. Try adding `Redim codeArr(1 to 1)` at the start of the code or see cpearons [IsArrayAllocated](http://www.cpearson.com/excel/isarrayallocated.aspx) – chris neilsen Jun 16 '16 at 23:08
  • codeArr is initialized as a Variant – Bubs Jun 17 '16 at 14:00

1 Answers1

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.

Matt Cremeens
  • 4,951
  • 7
  • 38
  • 67
  • 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? – Bubs Jun 16 '16 at 20:54
  • I made an edit. Should be `IsInArray(cells(i, 1).Value, codeArr)`. `ReDim Preserve ...` should work fine. Can we see your array? – Matt Cremeens Jun 16 '16 at 21:11
  • 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 – Bubs Jun 16 '16 at 21:21
  • What is in your array? – Matt Cremeens Jun 16 '16 at 22:04
  • And what is in column A? – Matt Cremeens Jun 16 '16 at 22:05
  • my array starts as empty and column A consist a bunch of 3 digit numbers. Most are the same, some may be different. And I would like to store the different ones – Bubs Jun 17 '16 at 13:59
  • I just checked the code and it's not enter this if statement: 'If Not IsInArray(cells(i, 1).Value, codeArr) Then' – Bubs Jun 17 '16 at 14:03
  • I added an edit last night that I hope will help guide you for your specific program. Please let me know if you have any questions about it. – Matt Cremeens Jun 17 '16 at 14:04
  • This is what I have so far `If IsInArray(cells(i, 1).Value, codeArr) Then ReDim Preserve codeArr(1 To cnt) codeArr(cnt) = cells(i, 1).Value cnt = cnt + 1 MsgBox codeArr End If' And the IsInArray function is as you suggested, but its going into the if at the moment. – Bubs Jun 17 '16 at 14:27
  • I believe you need `If Not ...` – Matt Cremeens Jun 17 '16 at 14:28
  • OK. Good. I was worried we had more work to do yet. Glad all is well. – Matt Cremeens Jun 17 '16 at 14:33