VBA : Get input from multiline box, match that input in a column

[ExcelJohn]

Dear All,

Could someone please give me some advice with the following problem ? I'm desperate.

I've created an VBA UserForm with a multiline Textbox. The user pastes a list of product ID's from a word document or an intranet page, and then that should be checked against the ID's on a column A. Then, write to a second multiline Textbox all the ID's that are not in that column A, so she can then copy and paste that purged list in another word document.

I guess the pseudo-code should be like that :

1.- Get TextBox1.Text (the whole list of ID's, one per line)
2.- Create an "arrayAll" whith those values (line by line)
3.- Purge that "arrayAll" from spaces, carriage returns, etc. So there's only ID numbers
4.- Create an empty "arrayValid" for future use

5.- Do a loop for every element in "arrayAll"

5a.- Is this ID in Column A ? We don't want it, next interation
5b.- Is this ID not in Column A ? We want it, we put it in "arrayValid", next interation.
​

6.- Now we've got "arrayValid" with the list of ID's that were not in Column A
7.- Show that list/array again in a second TextBox2.Text

Could someone please give me some hints ?

Thanks.

Best Regards,
John

 

[MickG]

If it is a "space" then this should trim all "Aphanumeric" strings.

Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] TxRay [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Lpray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] oLp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] st [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n
TxRay = Split(Replace(TextBox1, Chr(13), ""), Chr(10))
    [COLOR="Navy"]With[/COLOR] Sheets("Orders")
      [COLOR="Navy"]Set[/COLOR] rng = .Range(.Range("A1"), .Range("A" & rows.Count).End(xlUp))
    [COLOR="Navy"]End[/COLOR] With
        Ray = Application.Transpose(rng.value)
    
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
            Lpray = Array(Ray, TxRay)
    [COLOR="Navy"]For[/COLOR] oLp = 0 To UBound(Lpray)
            st = IIf(oLp = 1, 0, 1)
        [COLOR="Navy"]For[/COLOR] R = st To UBound(Lpray(oLp))
            [COLOR="Navy"]If[/COLOR] Not .Exists(Trim(Lpray(oLp)(R))) [COLOR="Navy"]Then[/COLOR]
                .Add Trim(Lpray(oLp)(R)), ""
                 [COLOR="Navy"]If[/COLOR] oLp = 1 [COLOR="Navy"]Then[/COLOR]
                    Txt = Txt & Trim(Lpray(oLp)(R)) & Chr(10)
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] oLp
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] TextBox2
  .MultiLine = True
   .value = Txt
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ExcelJohn]

Hey Mick! It works! Thanks again.

How would you do it the other way around ? I mean ...

When doing the comparsion, if a cell in Orders/ColA contains an ID with spaces before or after the string (i.e. " JK5679L2M1 ") the VBA code won't match it.

Is it possible to trim it before doing the comparsion, so the two ID's will be identical when compared ? Or something like that. I would prefer not to modify the column, just trim it for the VBA loops.

Is it possible ?

Big Cheers

 

[ExcelJohn]

Sorry Mick, forget my previous post. It works perfectly!

All the Best,
John

 

[MickG]

Hi, Not quite sure what you mean:-
The code places column "A" in Array "Ray" and the TextBox1 List in Array "TxRay".
When the code runs all the values in both Arrays are then trimmed and then compared, so if column "A" has " JK5679L2M1" (with space) and TextBox1 has "JK5679L2M1" (Without space) then because they are the same when trimmed this number will not appear in Textbox2.
If you are saying the number appears when you think both values are the same, then perhaps there's another character in the string that you can't see.
If that where the case you could do a check for all "AlphaNumeric" Characters.
Also the result in TextBox1 will only be what is in TextBox1 that is not in Column "A", and NOT, what is in Column "A" but not in TextBox1.
If that makes sense !!!
Mick

 

[ExcelJohn]

Mick, I get it now. Thanks a lot man. I feel I'm abusing your patience. Do you think it would be possible to remove all spaces for every string in the TxRay element ?

I'm asking this because I'm afraid the user could enter something like : "JK56 789L2 M1" instead of "JK56789L2M"

Cheers

 

[ExcelJohn]

I've tried this :

TxRay = Split(Replace(TextBox1, Chr(13), ""), Chr(10))
For i = LBound(TxRay) To UBound(TxRay)
    TxRay(i) = Replace(TxRay(i), " ", "")
Next

And it works!

Do you think it's safe ?

All the Best,
John

 

[MickG]

That looks excellent, I can see I'm a bit redundant now !!!
Mick

 

[ExcelJohn]

Hahaha No! After removing all spaces, how would I remove also the line breaks ? I have this

For i = LBound(TxRay) To UBound(TxRay)
    TxRay(i) = Replace(TxRay(i), " ", "")
    If TxRay(i) = vbCrLf Then
    
    End If
Next

But I don't know what to put inside the If. I don't know how to delete an arbitrary array element.

Any ideas ?

 

[MickG]

What do you get if you just add another replace as below to clear the spaces, seems to work for me.

TxRay = Split(Replace(Replace(TextBox1, Chr(13), ""), " ", ""), Chr(10))

Mick

 

[ExcelJohn]

Thanks Mick!! I owe you my day