PDA

View Full Version : [SOLVED] VBA Vlookup Reflect Data to TextBox1



VBWEE
10-15-2016, 09:36 AM
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
10-15-2016, 07:26 PM
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
10-16-2016, 05:57 AM
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
10-16-2016, 06:38 AM
Private Sub txtOption_AfterUpdate()
&
Private Sub txtmodel_AfterUpdate()


not
Private Sub TextBox1_Change()

mikerickson
10-16-2016, 06:50 AM
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
10-17-2016, 03:48 AM
Thanks, Master Mana. I got these. Million Thanks,

VBWEE
10-17-2016, 03:58 AM
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
10-17-2016, 06:14 AM
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
10-17-2016, 05:48 PM
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
10-18-2016, 03:00 AM
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
10-18-2016, 06:16 AM
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
10-18-2016, 05:45 PM
Hi, Master Mikerickson
Thanks for advice. However, it is compile error "ambiguous name detected: SortAndDelimit".

VBWEE
10-18-2016, 09:24 PM
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