1

I found this solution for my problem. And it works fine like this: 

Dim CNPJs As New Collection, a
Dim i As Long
Dim rng As Range

U_L = Plan2.Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next
For Each rng In Plan2.Range("A2:A" & U_L)
    CNPJs.Add rng.value, rng.value
Next

But now I want to bulid a function that returns this unique array, because I will use this many times... I tried this: 

Public Function unArray(myRange As Range) As Collection

    Dim arr As Collection, a

    On Error Resume Next
    For Each rng In myRange
        arr.Add rng.value, rng.value
    Next

    unArray = arr

End Function

And I call it:

Sub test()

      Dim CNPJs As New Collection, a

      U_L = Plan2.Range("A" & Rows.Count).End(xlUp).Row

      CNPJs = unArray(Plan2.Range("A2:A" & U_L))

End Sub

But it returns this error:

enter image description here
"Compilation error:"
"The argument is required"

Can you help me?

Community
  • 1
  • 1
Lugarini
  • 792
  • 5
  • 11
  • 32
  • 1
    I'm not 100% sure, but I think you need to use `Set`. `Set CNPJs = unArray(Plan2.Range("A2:A" & U_L))`... Also use `New` for the collection in the function... – Dirk Reichel Mar 09 '16 at 12:53
  • It works!!! Thanks man! I also had to use `Set` here: `Set unArray = arr` . Please post as answer and I will acept it. – Lugarini Mar 09 '16 at 12:58
  • I'm just on phone right now. But would need to explain why to post a suitable answer... maybe later... still, I don't mind the points. You got it to work, that is all that counts for me ;) – Dirk Reichel Mar 09 '16 at 13:10
  • ok, but I need to chose an answer, please post it :) – Lugarini Mar 10 '16 at 11:30

2 Answers2

1

A collection is an object and needs to be treated like one, means you need to Set it. But keep in mind that this action just creates a pointer to the range inside your RAM.
For your case just use:

Set CNPJs = unArray(Plan2.Range("A2:A" & U_L))

That said, it doesn't matter for your code, but having 2 collections while one is Set to the other, alternating one of them will also effect the other.

If you still have questions, just ask. :)

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
0
U_L = Plan2.Range("A" & Rows.Count).End(xlUp).Row

This will return a long or integer. A range requires a string and therefore you need to convert U_L to a string when referencing it in a range.

CNPJs = unArray(Plan2.Range("A2:A" & CStr(U_L)))
iShaymus
  • 512
  • 7
  • 26
  • Unfortunately that is not true. `"A" & variable` will be a string in the first place. (As long as `variable` is no object which would throw an error (still will work if the object is a pointer to a non-object variable like `Range` or `Cells`)). At the other hand, `Range(Cells(1 ,1))` will also work and it is just a pointer (no string or numerical variable). ;) – Dirk Reichel Mar 10 '16 at 12:48
  • Is this a recent change to excel, because I have certainly received errors by doing this in the past. I don't understand how you can concatenate a string with an integer and not get an error – iShaymus Mar 10 '16 at 13:43