INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel VBA userform Merging 2 Arrays together and removing duplicates

Excel VBA userform Merging 2 Arrays together and removing duplicates

(OP)
Hi Guys,

I am building a form in Excel that has 2 textboxes that hold values separated by ";". What I am wanting is to read each of the textbox values and store them as arrays, then to combine them in to a 3rd array whilst removing duplicates. What I have so far doesn't seem to work for creating the combined array.

So the function I am using is:

CODE --> VBA

Function MergeArrays(vFirstArray As Variant, vSecondArray As Variant) As Variant

    Dim vMergedArray() As Variant
    Dim iFirstArrayLen As Integer
    Dim iSecondArrayLen As Integer
    Dim iMergedArrayLength As Integer
    Dim iCounter As Integer
    
    iFirstArrayLen = UBound(vFirstArray)
    iSecondArrayLen = UBound(vSecondArray)
    iMergedArrayLength = iFirstArrayLen + iSecondArrayLen
    
    ReDim vMergedArray(0 To iMergedArrayLength)
    iCounter = 1

    Do While iCounter <= iFirstArrayLen
        vMergedArray(iCounter) = vFirstArray(iCounter)
        iCounter = iCounter + 1
    Loop
    Do While iCounter <= iMergedArrayLength
        vMergedArray(iCounter) = vSecondArray(iCounter - iFirstArrayLen)
        iCounter = iCounter + 1
    Loop
    
    MergeArrays = vMergedArray
    
End Function 

I am then testing this by using the following code

CODE --> VBA

Option Base 1
Private Sub Image1_Click()
    Dim vFirstArray As Variant
    Dim vSecondArray As Variant
    Dim vArray3  As Variant
    
    vFirstArray = Split(selectedClosureReasonsTxt.Text, ";")
    vSecondArray = Split(selectedRecallReasonsTxt.Text, ";")
    
        vArray3 = MergeArrays(vFirstArray, vSecondArray)
    
    Dim iCounter As Integer
    
    For iCounter = 1 To UBound(vArray3)
        Debug.Print iCounter & " " & vArray3(iCounter)
    Next iCounter
    
End Sub 

selectedClosureReasonsTxt.Text currently has "ABR;ACS;"
selectedRecallReasonsTxt.Text currently has "ABR;DIS;"

When I run the code my output is:
1 ACS
2
3 DIS
4

Which to me seems as though it is not reading the data in the first element in the Array. Does anyone have any ideas how to resolve this? Or can propose a better solution so that I end up with a final list of unique values from both textboxes in an array

Many Thanks in advance

Regards

J.

RE: Excel VBA userform Merging 2 Arrays together and removing duplicates

Jason,

1. Why start with Counter = 1? You loose your first element.

2. Why end the strings with ;? this adds an empty element.

Each of your arrays has an upper bound of 2, meaning that there are 3 elements. The lower bound for each is 0. So when combining, UBound + 1 for each array, then finally subtract 1.

And my results...
0 ABR
1 ACS
2 ABR
3 DIS
 


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel VBA userform Merging 2 Arrays together and removing duplicates

Why do you complicate this so much? Depending on the requirement and meaning of semicolon at the end, you can
- combine textboxes texts first with "&",
- split,
- use collection to generate unique list with filtering empty strings if necessary.

combo

RE: Excel VBA userform Merging 2 Arrays together and removing duplicates

(OP)
Hi Guys,

The reason for the ";" is that I use a listview with checkboxes for the user to select a choice of codes, this then populates the textbox. The ";" separation means I can display them to the user in a format they are familiar with and can display a vast number of codes in the single textbox without having to scroll (both textboxes are controlled by different listboxes). From the user input I then use the distinct values to generate SQL code.

Skip thanks, that has solved my issue. I can't believe I missed the Ubound + 1...

Combo thanks for also pointing out how I had over complicated the joining of the two inputs. Once again I had fallen victim to the bad habit of jumping in to code without fully planning what I wanted and started running with my first idea.

Thanks again guys, as always I appreciate your wise words of wisdom.

Regards

J.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close