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?

4
  • 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... Commented Mar 9, 2016 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. Commented Mar 9, 2016 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 ;) Commented Mar 9, 2016 at 13:10
  • ok, but I need to chose an answer, please post it :) Commented Mar 10, 2016 at 11:30

2 Answers 2

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. :)

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

Comments

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)))

2 Comments

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). ;)
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.