none
Excel Macro: userform to vlookup value from textbox1 & textbox2 in sheet("name") & return value in another testbox3 RRS feed

  • 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


    Thursday, November 3, 2016 10:40 AM

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 add-ins & 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
    Thursday, November 3, 2016 7:26 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
    Thursday, November 3, 2016 9:07 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
    Thursday, November 3, 2016 9:16 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
    Thursday, November 3, 2016 10:10 PM

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 add-ins & 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
    Thursday, November 3, 2016 7:26 PM
  • Hi,

    I would recommend you to solve this problem simply bu using excel built-in 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

    Thursday, November 3, 2016 7:45 PM
  • 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

    Thursday, November 3, 2016 8:43 PM
  • 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


    Thursday, November 3, 2016 8:48 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
    Thursday, November 3, 2016 9:07 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
    Thursday, November 3, 2016 9:16 PM
  • Thanks alot Vish,

    Is there a possiblity to directly get the textbox3 value instead of clicking a button? So if textbox1.value <>0 & textbox2.value<>0 then textbox3 = sub()- I had added button just for testing.

    NewBInVB

    Thursday, November 3, 2016 9:58 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
    Thursday, November 3, 2016 10:10 PM
  • Thanks alot Vish

    NewBInVB

    Saturday, November 5, 2016 8:18 AM