Answered by:
Excel Macro: userform to vlookup value from textbox1 & textbox2 in sheet("name") & return value in another testbox3
Question

Hi,
1. I have a worksheet in Thisworkbook called lookup
2. I have an userform with 3 textboxs where user will provide textbox 1 & textbox2 values if found in lookup columnc then textbox3 must return the vlookup value from col c from above sheet
t
3. the lookup table rows are dynamic but columns are fixed so I have written the following code to get the range, textbox1 & textbox2 values then passing it to a function to get the vallue in textbox 3
But the code is not working nor I understand how to pass 2 conditions from textbox to vlookup & return value in 3rd box
Private Sub CommandButton1_Click() Dim wb As Workbook Dim sht As Worksheet Dim userange As Range Dim lastrow As Long Dim lastcolumn As Long Dim startcell As Range 'Finding the dynamic table range in sheet lookup Set sht = ThisWorkbook.Worksheets("lookup") Set startcell = Range("A1") 'Find Last Row and Column lastrow = sht.Cells(sht.Rows.Count, startcell.Column).End(xlUp).Row lastcolumn = sht.Cells(startcell.Row, sht.Columns.Count).End(xlToLeft).Column 'select range userange = sht.Range(startcell, sht.Cells(lastrow, lastcolumn)).Select 'Constraints from 2 textboxs given in userform Dim cit1 As String cit1 = TextBox1.Value 'textbox1 Dim cit2 As String cit2 = TextBox2.Value 'textbox2 'calling vlookup function by passing the lookup range from above, return value in col D if col B in excel sheet(lookup) 'has textbox 1.value & col C in excel sheet(lookup) has textbox2.value Two_Con_Vlookup(userange,D,cit1,cit2) End Sub Function Two_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd) Dim rCheck As Range, bFound As Boolean, lLoop As Long On Error Resume Next Set rCheck = Table_Range.Columns(1).Cells(1, 1) With WorksheetFunction For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd) Set rCheck = Table_Range.Columns(1).find(Col1_Fnd, rCheck, xlValues, xlWhole, xlNext, xlRows, False) If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) Then bFound = True Exit For End If Next lLoop End With If bFound = True Then Two_Con_Vlookup = rCheck(1, Return_Col) 'textbox3 must return with col D TextBox3 = Two_Con_Vlookup.Value Else Two_Con_Vlookup = "#N/A" TextBox3 = Two_Con_Vlookup.Value End If End Function
NewBInVB
 Moved by KareninstructorMVP Thursday, November 3, 2016 11:06 AM Moved from VB.NET
 Edited by NewBInCoding Thursday, November 3, 2016 1:33 PM
Answers

NewBInVB,
Re: code problems
You will get much better advice if you specify what "not working" means.
Do you get an error? If so, what error?, what does it say?, on what line does it occur?
With that information, you might be able to attack the problem yourself.
[Edited]...
You must have "on error resume next" commented out and or "Break on all Errors" checked in the VBE when developing code or you could end up here with me bagering you about it. <grin>
Some problem areas in your code do show up...
Set startcell = Range("A1") should be Set startcell = sht. Range("A1")
You are passing "D" to the function and the function is expecting a number (Long)... try 4 instead of D.
userange = sht.Range(startcell, sht.Cells(lastrow, lastcolumn)).Select should be Set userange = sht.Range(startcell, sht.Cells(lastrow, lastcolumn))
'
Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox)(free & commercial excel addins & workbooks)
 Edited by James Cone Thursday, November 3, 2016 7:39 PM added break on errors
 Marked as answer by NewBInCoding Thursday, November 3, 2016 8:43 PM

Hi,
while calling this function you should not use all the parameters in bracket because your function is not returning any value.
Two_Con_Vlookup userange,3,1 = cit1,2 = cit2
Just remove the opening and closing bracket
Vish Mishra
 Marked as answer by NewBInCoding Thursday, November 3, 2016 9:55 PM

Hi In your code there were some minor issues as well ... here is the full working code:
Function Two_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd) As Variant Dim rCheck As Range, bFound As Boolean, lLoop As Long 'On Error Resume Next Set rCheck = Table_Range.Columns(1).Cells(1, 1) With WorksheetFunction For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd) Set rCheck = Table_Range.Columns(1).Find(Col1_Fnd, rCheck, xlValues, xlWhole, xlNext, xlRows, False) If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) Then bFound = True Exit For End If Next lLoop End With If bFound = True Then Two_Con_Vlookup = rCheck(1, Return_Col) Else Two_Con_Vlookup = "Match Not Found" End If End Function
Button Code:
Private Sub CommandButton1_Click() ' [input1] = Me.TextBox1.Text ' [input2] = Me.TextBox2.Text ' Application.Calculate 'Me.TextBox3.Text = [result] Dim wb As Workbook Dim sht As Worksheet Dim userange As Range Dim lastrow As Long Dim lastcolumn As Long Dim startcell As Range 'Finding the dynamic table range in sheet lookup Set sht = ThisWorkbook.Worksheets("lookup") Set startcell = Range("A1") 'Find Last Row and Column lastrow = sht.Cells(sht.Rows.Count, startcell.Column).End(xlUp).Row lastcolumn = sht.Cells(startcell.Row, sht.Columns.Count).End(xlToLeft).Column 'select range Set userange = sht.Range(startcell, sht.Cells(lastrow, lastcolumn)) 'Constraints from 2 textboxs given in userform Dim cit1 As String cit1 = TextBox1.Value 'textbox1 Dim cit2 As String cit2 = TextBox2.Value 'textbox2 'calling vlookup function by passing the lookup range from above, return value in col D if col B in excel sheet(lookup) 'has textbox 1.value & col C in excel sheet(lookup) has textbox2.value TextBox3.Value = Two_Con_Vlookup(userange, 3, cit1, cit2) End Sub
Vish Mishra
 Marked as answer by NewBInCoding Thursday, November 3, 2016 9:55 PM

Yes this is possible.
You can the following code in Change event of both the textbox 1 and 2
Private Sub TextBox1_Change() If TextBox1.Text <> "" And TextBox2.Text <> "" Then TextBox3.Value = Two_Con_Vlookup(userange, 3, cit1, cit2) End If End Sub Private Sub TextBox2_Change() If TextBox1.Text <> "" And TextBox2.Text <> "" Then TextBox3.Value = Two_Con_Vlookup(userange, 3, cit1, cit2) End If End Sub
Vish Mishra
 Marked as answer by NewBInCoding Saturday, November 5, 2016 8:18 AM
All replies

NewBInVB,
Re: code problems
You will get much better advice if you specify what "not working" means.
Do you get an error? If so, what error?, what does it say?, on what line does it occur?
With that information, you might be able to attack the problem yourself.
[Edited]...
You must have "on error resume next" commented out and or "Break on all Errors" checked in the VBE when developing code or you could end up here with me bagering you about it. <grin>
Some problem areas in your code do show up...
Set startcell = Range("A1") should be Set startcell = sht. Range("A1")
You are passing "D" to the function and the function is expecting a number (Long)... try 4 instead of D.
userange = sht.Range(startcell, sht.Cells(lastrow, lastcolumn)).Select should be Set userange = sht.Range(startcell, sht.Cells(lastrow, lastcolumn))
'
Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox)(free & commercial excel addins & workbooks)
 Edited by James Cone Thursday, November 3, 2016 7:39 PM added break on errors
 Marked as answer by NewBInCoding Thursday, November 3, 2016 8:43 PM

Hi,
I would recommend you to solve this problem simply bu using excel builtin features like formulas or form controls etc. unless it is really reaquired to use VBA and UserForms etc.
If you want with UserForm then you can follow the following Steps:
1. Create a helper column in Column A by concatenating TextBox1 and TextBox2 column value
2. Also in your workbook give Name to any 3 cells like this "input1", input2" and "result" like highlighted in the below sheet image
3. Now in "result" cell add this formula:
=IFERROR(VLOOKUP(input1&input2,A1:D9,4,FALSE),"No Match Found")
4. Now add the following VBA code for the commandButton added in the UserForm:
Private Sub CommandButton1_Click() [input1] = Me.TextBox1.Text [input2] = Me.TextBox2.Text Application.Calculate Me.TextBox3.Text = [result] End Sub
Hope this helps
Vish Mishra

Thanks Vish,
The problem in using your solution is the lookup rows are very huge 10000+ and the value must be passed to the textbox 3itself and not any cell in the sheet. Based on the lookup return value the user decides to add userform details to another worksheet as a row.NewBInVB

Thanks James I have added my code but,
The error that occurs now is Expected:= at line
Two_Con_Vlookup(userange,3,1 = cit1,2 = cit2)
I tried
Two_Con_Vlookup(userange,3, cit1, cit2)
Two_Con_Vlookup(userange,3,colA = cit1,colB = cit2)
Two_Con_Vlookup(userange,3,col(1) = cit1,col(2) = cit2)
Two_Con_Vlookup(userange,3,cit1,cit2)
NewBInVB
 Edited by NewBInCoding Thursday, November 3, 2016 8:49 PM

Hi,
while calling this function you should not use all the parameters in bracket because your function is not returning any value.
Two_Con_Vlookup userange,3,1 = cit1,2 = cit2
Just remove the opening and closing bracket
Vish Mishra
 Marked as answer by NewBInCoding Thursday, November 3, 2016 9:55 PM

Hi In your code there were some minor issues as well ... here is the full working code:
Function Two_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd) As Variant Dim rCheck As Range, bFound As Boolean, lLoop As Long 'On Error Resume Next Set rCheck = Table_Range.Columns(1).Cells(1, 1) With WorksheetFunction For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd) Set rCheck = Table_Range.Columns(1).Find(Col1_Fnd, rCheck, xlValues, xlWhole, xlNext, xlRows, False) If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) Then bFound = True Exit For End If Next lLoop End With If bFound = True Then Two_Con_Vlookup = rCheck(1, Return_Col) Else Two_Con_Vlookup = "Match Not Found" End If End Function
Button Code:
Private Sub CommandButton1_Click() ' [input1] = Me.TextBox1.Text ' [input2] = Me.TextBox2.Text ' Application.Calculate 'Me.TextBox3.Text = [result] Dim wb As Workbook Dim sht As Worksheet Dim userange As Range Dim lastrow As Long Dim lastcolumn As Long Dim startcell As Range 'Finding the dynamic table range in sheet lookup Set sht = ThisWorkbook.Worksheets("lookup") Set startcell = Range("A1") 'Find Last Row and Column lastrow = sht.Cells(sht.Rows.Count, startcell.Column).End(xlUp).Row lastcolumn = sht.Cells(startcell.Row, sht.Columns.Count).End(xlToLeft).Column 'select range Set userange = sht.Range(startcell, sht.Cells(lastrow, lastcolumn)) 'Constraints from 2 textboxs given in userform Dim cit1 As String cit1 = TextBox1.Value 'textbox1 Dim cit2 As String cit2 = TextBox2.Value 'textbox2 'calling vlookup function by passing the lookup range from above, return value in col D if col B in excel sheet(lookup) 'has textbox 1.value & col C in excel sheet(lookup) has textbox2.value TextBox3.Value = Two_Con_Vlookup(userange, 3, cit1, cit2) End Sub
Vish Mishra
 Marked as answer by NewBInCoding Thursday, November 3, 2016 9:55 PM


Yes this is possible.
You can the following code in Change event of both the textbox 1 and 2
Private Sub TextBox1_Change() If TextBox1.Text <> "" And TextBox2.Text <> "" Then TextBox3.Value = Two_Con_Vlookup(userange, 3, cit1, cit2) End If End Sub Private Sub TextBox2_Change() If TextBox1.Text <> "" And TextBox2.Text <> "" Then TextBox3.Value = Two_Con_Vlookup(userange, 3, cit1, cit2) End If End Sub
Vish Mishra
 Marked as answer by NewBInCoding Saturday, November 5, 2016 8:18 AM
