VBA Vlookup Reflect Data to TextBox1

[VBWEE]

Hi, Experts
I am need helps. I had named a sheet "Data". I had created an Userform to enter data as txtmodel & txtoption. However, I am need answer "Match" or "Not Match" to be display at the e=TextBox1 which are refer to Data Sheet column C. Please help & advice. Thank you.

[mana]

Dim r As Range, c As Range
Dim flg As Boolean


Set r = Worksheets("DATA").CurrentRegion.Resize(, 1)


For Each c In r
    If c.Value = txtmodel.Text Then
        If c.Offset(, 1).Value = txtOption.Text Then
            flg = True
            Exit For
        End If
    End If
Next


If flg Then
    TextBox1.Text = "Match"
Else
    TextBox1.Text = "NotMatch"
End If

[VBWEE]

Thanks, Master Mana

However, I pasted it into the TextBox1. It does not work. Please advice. Appreciate your helps.

Private Sub TextBox1_Change()


Dim r As Range, c As Range
Dim flg As Boolean
 
 
Set r = Worksheets("DATA").CurrentRegion.Resize(, 1)
 
 
For Each c In r
    If c.Value = txtmodel.Text Then
        If c.Offset(, 1).Value = txtOption.Text Then
            flg = True
            Exit For
        End If
    End If
Next
 
 
If flg Then
    TextBox1.Text = "Match"
Else
    TextBox1.Text = "NotMatch"
End If


End Sub

[mana]

Private Sub txtOption_AfterUpdate()
&
Private Sub txtmodel_AfterUpdate()


not
Private Sub TextBox1_Change()

[mikerickson]

I have a couple of questions.
In the sheet Data, it looks like column C is getting its value by checking sheet Table.
It appears that Data!C:C checks to see it the options in Data!B:B are listed in Table!A:B as going with the particular item.

I'm a bit curious. Table looks like the options are all three letter codes, delimited by comma-space. But your entry in Data!B2 is not delimited at all.

Are all option codes supposed to be comma-space delimited 3 letter codes?
Is it always 3 letters, never more never less?
Do your users frequently forget to type the commas and spaces?

The reason that I ask is that when entering in the user form, do you want the uf to automatically parse the entry in txtOption, making sure that there is a delimiter between every 3 letter code?

Also, what do you want to trigger the "match"/"no match" entry in TextBox1? Do you want that to happen as the user types into txt model or txtOption or do you want them to press the Check button?

[VBWEE]

Thanks, Master Mana. I got these. Million Thanks,

[VBWEE]

Hi, Master Mikerickson
You are right, the sheet Table is used as reference table. However, the info enter into txtOption.text will be words without comma & space. Hereby, the sheet "Data" had created to substitute the comma & space for determine the entries "Match" or "Not Match" with reference sheet "Table". Thanks,

[mikerickson]

I came up with this which parses changes txtOption to the delimited format in sheet Table and tests the entries directly against that sheet rather than sheet Data.

Private Sub txtmodel_AfterUpdate()
    Call Input_AfterUpdate
End Sub

Private Sub txtOption_AfterUpdate()
    Call Input_AfterUpdate
End Sub

Private Sub Input_AfterUpdate()
    If txtOption.Text = vbNullString Or txtModel.Text = vbNullString Then
        TextBox1.Text = "-"
    Else
        txtOption.Text = SortAndDelimit(txtOption.Text)
        With Sheets("Table")
            If 0 < WorksheetFunction.CountIfs(.Range("A:A"), txtModel.Text, .Range("B:B"), txtOption.Text) Then
                TextBox1.Text = "Match"
            Else
                TextBox1.Text = "No Match"
            End If
        End With
    End If
End Sub

Function SortAndDelimit(ByVal strInput As String, Optional Delimiter As String = ", ") As String
    Dim i As Long, j As Long, WordCount As Long
    Dim Words As Variant
    For i = 1 To Len(Delimiter)
        strInput = Replace(strInput, Mid(Delimiter, i, 1), vbNullString)
    Next i
    strInput = UCase(strInput)
    
    WordCount = Int((Len(strInput) - 1) / 3)
    ReDim Words(0 To WordCount)
    For i = 0 To UBound(Words)
        Words(i) = Mid(strInput, 3 * i + 1, 3)
    Next i
    
    For i = 0 To WordCount - 1
        For j = i + 1 To WordCount
            If Len(Words(i)) < Len(Words(j)) Then
                GoSub Swap
            ElseIf Len(Words(i)) = Len(Words(j)) Then
                If (Words(j) < Words(i)) Then GoSub Swap
            End If
        Next j
    Next i
    
    SortAndDelimit = Join(Words, Delimiter)
    Exit Function
Swap:
    strInput = Words(i)
    Words(i) = Words(j)
    Words(j) = strInput
    Return
End Function

[VBWEE]

Hi, Master Mikerickson
This would be great. It is working well & smooth. But I am need to digest a lot of the code you used. Learnt a lot from here. Thanks.

Thanks to Master Mana as well. Both of you are great master.

[VBWEE]

Hi, Master Mikerickson
After verifying the result. Found there is problem when enter info with "empty Option. And I am try to analysis the code. But due to weak in VBA, I still need your helps to advice. Thanks.

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Trim(Me.txtmodel.Value) = "" Then
  Me.txtmodel.SetFocus
  MsgBox "Please Enter a Model Number"
  Exit Sub
End If

With ws
  .Cells(iRow, 1).Value = Me.txtmodel.Value
  .Cells(iRow, 2).Value = Me.txtOption.Value
End With

Me.txtmodel.Value = ""
Me.txtOption.Value = ""
Me.txtmodel.SetFocus
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub txtmodel_AfterUpdate()
    Call Input_AfterUpdate
End Sub
 
Private Sub txtOption_AfterUpdate()
    Call Input_AfterUpdate
End Sub
 
Private Sub Input_AfterUpdate()
    If txtOption.Text = vbNullString Or txtmodel.Text = vbNullString Then
        TextBox1.Text = "    * - *    "
    Else
        txtOption.Text = SortAndDelimit(txtOption.Text)
        With Sheets("Table")
            If 0 < WorksheetFunction.CountIfs(.Range("A:A"), txtmodel.Text, .Range("B:B"), txtOption.Text) Then
                TextBox1.Text = "   Match"
            Else
                TextBox1.Text = "Not Match"
            End If
        End With
    End If
End Sub
 
Function SortAndDelimit(ByVal strInput As String, Optional Delimiter As String = ", ") As String
    Dim i As Long, j As Long, WordCount As Long
    Dim Words As Variant
    For i = 1 To Len(Delimiter)
        strInput = Replace(strInput, Mid(Delimiter, i, 1), vbNullString)
    Next i
    strInput = UCase(strInput)
     
    WordCount = Int((Len(strInput) - 1) / 3)
    ReDim Words(0 To WordCount)
    For i = 0 To UBound(Words)
        Words(i) = Mid(strInput, 3 * i + 1, 3)
    Next i
     
    For i = 0 To WordCount - 1
        For j = i + 1 To WordCount
            If Len(Words(i)) < Len(Words(j)) Then
                GoSub Swap
            ElseIf Len(Words(i)) = Len(Words(j)) Then
                If (Words(j) < Words(i)) Then GoSub Swap
            End If
        Next j
    Next i
     
    SortAndDelimit = Join(Words, Delimiter)
    Exit Function
Swap:
    strInput = Words(i)
    Words(i) = Words(j)
    Words(j) = strInput
    Return
End Function

Private Sub UserForm_Click()
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the Exit button!"
  End If
End Sub

[mikerickson]

Insert this line

Function SortAndDelimit(ByVal strInput As String, Optional Delimiter As String = ", ") As String 
    Dim i As Long, j As Long, WordCount As Long 
    Dim Words As Variant 
    For i = 1 To Len(Delimiter) 
        strInput = Replace(strInput, Mid(Delimiter, i, 1), vbNullString) 
    Next i 
    strInput = UCase(strInput)

    If strInput = vbNullString then Exit Function: rem <<<<<<<<

'...
End Function

[VBWEE]

Hi, Master Mikerickson
Thanks for advice. However, it is compile error "ambiguous name detected: SortAndDelimit".

[VBWEE]

Thanks, Master Mikerickson
I had inserted the line looked as below. It is worked. Millions Thanks,

Function SortAndDelimit(ByVal strInput As String, Optional Delimiter As String = ", ") As StringDim i As Long, j As Long, WordCount As Long
    Dim Words As Variant
    For i = 1 To Len(Delimiter)
        strInput = Replace(strInput, Mid(Delimiter, i, 1), vbNullString)
    Next i
    strInput = UCase(strInput)
     
    If strInput = vbNullString Then Exit Function:
     
    WordCount = Int((Len(strInput) - 1) / 3)
    ReDim Words(0 To WordCount)
    For i = 0 To UBound(Words)
        Words(i) = Mid(strInput, 3 * i + 1, 3)
    Next i
     
    For i = 0 To WordCount - 1
        For j = i + 1 To WordCount
            If Len(Words(i)) < Len(Words(j)) Then
                GoSub Swap
            ElseIf Len(Words(i)) = Len(Words(j)) Then
                If (Words(j) < Words(i)) Then GoSub Swap
            End If
        Next j
    Next i
     
    SortAndDelimit = Join(Words, Delimiter)
    Exit Function
Swap:
    strInput = Words(i)
    Words(i) = Words(j)
    Words(j) = strInput
    Return
End Function