While adding new values to an array I'd like to check with each new value if it already exists in the array.
The following however throws an error on this line
Please Login or Register to view this content.invalid procedure call or argumentLink to filePlease Login or Register to view this content.
Last edited by Jonathan78; 08-25-2017 at 04:03 AM. Reason: Link to file added
I think you have to ReDim the array (as 2-dimensional??) BEFORE using it with Match:Please Login or Register to view this content.
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
Hi Jonathan,
If you have an array of numbers in Excel from say B2 to N7, another method to check if there are duplicates would be to use the frequency formula
=Max(Frequency(B2:N7,B2:N7)
If this is over 1 then there is a duplicate somewhere in your array of numbers.
You could also use Conditional Formatting and set it to find duplicates.
If you want to do it using VBA the above might be usless information.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
On the first iteration of the loop the array will be empty, not sure you can check for a value in an empty array.
If posting code please use code tags, see here.
None of the above solved the problem.
I added the file to my post as an example.
Why not use a Dictionary ? Much easier to create unique list.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
I need the array to perform tasks somewhere else in the code
Like this.
Please Login or Register to view this content.
Last edited by bakerman2; 08-26-2017 at 06:49 AM.
Thanks bakerman2 I will give it a try once I am at my computer again.
Can anyone tell me why my code didn't work?
And is it possible to get it work with perhaps a small adjustment?
The reason you get the error you describe is because the array is empty, and undimensioned, on the first iteration of the loop, just as I suggested in my original post
Originally Posted by Norie
Thanks for explaining that Norie.
How do I overcome this?
On the first iteration of the loop you don't need to check if the value you are trying to add to the array is already in the array.
So you could either skip the code to check for a duplicate on the first iteration or, and perhaps better, populate the array with the first value in the range before you start the loop.
That second option might look something like this.
Please Login or Register to view this content.
That worked, Thank!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks