2

I am just new in excel VBA and I really need help about the code I am using .Any help is appreciated.

I have a VBA UserForm with a multiline Textbox. The user pastes a list of values into it. Then, those values will be checked comparing values in column A. Then, the compared output will be display into Textbox2 . It means if some values in Textbox1 are same with the values with Column A it will be display in Textbox2 . All values that are not existing in Column A that have in Textbox1 will not be included. The code I am using is working. But its output are those values that are not existing in Column A instead of the similar values.

Here's the code:

Private Sub CommandButton1_Click()

Dim TxRay As Variant
Dim Lpray As Variant
Dim oLp As Integer
Dim rng As Range
Dim R As Integer
Dim Ray As Variant
Dim Txt As String
Dim st As Integer
Dim n

TxRay = Split(Replace(TextBox1, Chr(13), ""), Chr(10))
    With Sheets("Orders")
      Set rng = .Range(.Range("A1"), .Range("A" & rows.Count).End(xlUp))
    End With
        Ray = Application.Transpose(rng.value)

    With CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
            Lpray = Array(Ray, TxRay)
    For oLp = 0 To UBound(Lpray)
            st = IIf(oLp = 1, 0, 1)
        For R = st To UBound(Lpray(oLp))
            If Not .Exists(Trim(Lpray(oLp)(R))) Then
                .Add Trim(Lpray(oLp)(R)), ""
                 If oLp = 1 Then
                    Txt = Txt & Trim(Lpray(oLp)(R)) & Chr(10)
                End If
        End If
Next R
Next oLp
End With
With TextBox2
  .MultiLine = True
   .value = Txt
End With
End Sub

enter image description here

Improve this question

2 Answers 2

Reset to default
3

Here is how I would do it:

enter image description here

Private Sub CommandButton1_Click()
    Dim key As Variant, vlist As Variant, v As Variant
    Dim text As String
    Dim dict As Object
    Set dict = getValueDictionary
    vlist = Split(Replace(TextBox1, Chr(13), ""), Chr(10))

    For Each v In vlist
        key = Trim(v)
        If dict.Exists(key) Then text = text & key & Chr(10)
    Next

    With TextBox2
        .MultiLine = True
        .Value = Left(text, Len(text) - 1) ' Removes the last Chr(10)
    End With
End Sub

Function getValueDictionary() As Object
    Dim key As Variant, v As Variant
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    With Sheets("Orders")
        For Each v In .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Value
            key = Trim(v)
            If Not dict.Exists(key) Then dict.Add key, vbNullChar
        Next
    End With
    Set getValueDictionary = dict
End Function

Additional Functionality added: Remove values from Textbox1 as they are added to the Textbox2 values.

Private Sub CommandButton1_Click()
    Dim key As Variant, vlist As Variant, v As Variant
    Dim text As String
    Dim dict As Object
    Set dict = getValueDictionary
    vlist = Split(Replace(TextBox1, Chr(13), ""), Chr(10))

    For Each v In vlist
        key = Trim(v)
        If dict.Exists(key) Then
            text = text & key & Chr(10)
            TextBox1.text = Replace(TextBox1.text, key & Chr(13) & Chr(10), "")
        End If
    Next

    With TextBox2
        .MultiLine = True
        .Value = Left(text, Len(text) - 1)            ' Removes the last Chr(10)
    End With
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Hi Thomas , removing the not is not working for me no any output in Textbox2 . but i will try to test your code and inform you later . Thank you.
Hi Thomas your code perfectly work . Thank you for your help . But before letting go this thread can I ask another question? IF example , I just want use my original code. Then the thing I want to happen is to delete the difference in textbox1 which is the output in textbox2 . What code should I add? what I mean is textbox2 output is the different between column A and textbox1 then if the code found the difference it will be put in textbox2 and delete in textbox1.
The problem with your code is that you have an Array of arrays. I modified my code to suite your needs.
You should watch this series on Youtube: Excel VBA Introduction
Hi Thomas thanks for your explanation. I already test your added code it is working base on your description but I got an error code 5 Invalid procedure call or argument in .Value = (text,Len(text) -1) . Actually what I am asking is to remove the 700 in textbox1 but the output in textbox2 will be 700 because it is not existing in column A. So far the added code is still usefull and thanks for that.
|
0

Remove the Not from this line and try again:

If Not .Exists(Trim(Lpray(oLp)(R))) Then
Improve this answer

1 Comment

Hi David.. Actually I already tried that one many times . It will not have any error and the result is null . there will be no any output in Textbox2.. Thank you.

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.