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]

Try this:-
Place in command Button on Userform:-
Userform has TextBoxes (1 & 2)
To copy to a Textbox I had to "Ctrl C" & "Ctrl V", How do you do it ??
This code will place the Values in TextBox1, that are not in column "A", in TextBox2".

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"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
        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(Val(Lpray(oLp)(R))) [COLOR="Navy"]Then[/COLOR]
                .Add Val(Lpray(oLp)(R)), ""
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]If[/COLOR] oLp = 1 [COLOR="Navy"]Then[/COLOR]
                    Txt = Txt & 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]

Mick,

First of all, thank you very much for your prompt reply.

Yes, I do use Ctrl+V to paste the ID's into TextBox1 from the Word Document.

Unfortunately, this is not working. The code outputs to TextBox2 all the input in TextBox1, so that means the check is not working. Can it be because ID's are alphanumeric ? (I forgot to mention that, sorry) Maybe changing the variable type ?

Best Regards

 

[MickG]

Hi, Two things:-
First my code (if it had worked ) would have put the duplicates (Not what you wanted) in "Textbox2" and secondly Being "Alpanumeric" made a difference.
Below is the replacement code.
I can't tell how your copied Text in "Textbox1" is formatted, but if you first type the "Alphanumeric" data into column "B " and then copy this to "TextBox1", I know from my trials that this text will have "Chr(13) and Chr(10) [Carriage return + Linefeed] in the string.
If you then get an error when you try with the real text, it should be its formatting.
One step at a time !!!

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"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
        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(Lpray(oLp)(R)) [COLOR="Navy"]Then[/COLOR]
                .Add Lpray(oLp)(R), ""
                 [COLOR="Navy"]If[/COLOR] oLp = 1 [COLOR="Navy"]Then[/COLOR]
                    Txt = Txt & 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] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ExcelJohn]

http://www.2shared.com/document/qfGcMzEz/userforms.htmlThanks Mick I'm going to try it

 

[ExcelJohn]

Mick,

This is WONDERFUL!. Thank you very much! You saved my day.

It works even if I paste formatted text (in fact TextBox1 unformats everything you paste into it). It also removes duplicates from TextBox1, that I didn't request but it's great anyway.

Two last things :
1- Right now it uses column A from the current sheet. What if I wanted to always specifically use the sheet called "Orders" ? This is just in case the user adds more sheets.

2- Do you think this code would be easily adapted to a HTA script ? It would be even more simple for the users (they're not technical).

Well, thanks again man.

Best Regards,
John

 

[MickG]

Hi, Please its working for you.
To reflect a specific sheet for "Rng" try amending the "Set Rng", bit of code ,to the code below. I should copy it into the old code . This will make sure you keep all the "." , there very important.
With regard to "HTA" I'm afraid you've discovered my limit.
I should try with a new General thread, someone will know .!!

With Sheets("Orders")
Set Rng = .Range(.Range("A1"), .Range("A" & rows.Count).End(xlUp))
End With

Regards Mick

 

[ExcelJohn]

Thanks again Mick, it works.

I've tried it in the real file (~50k rows) and I get an overflow error. I've changed "oLP" variable and "R" variable from Integer to Long and it works again. Do you think it's safe to keep it like that ?

Best

 

[MickG]

If I'd realised the size of the data I would have made it "Long" to start with.
Regards Mick

 

[ExcelJohn]

Hi Mick,

I would like to ask you for one last thing.

I would like to remove all spaces (like the Trim function) for every string placed in the TxRay array because if the alphanumeric ID's on TextBox1 have spaces, they are not matched correctly, that is, they're shown in TextBox2.

Where should I place that Trim ? Or how should I avoid that behaviour ?

Cheers