textbox1 & textbox2中给出的值的Vlookup &来自sheet2的另一个testbox3中的返回值

Question

我需要excel vba代码中的帮助，vlookup的两个值来自textbox 1& textbox 2。

1. 我在这个工作簿中有一个叫做查找(动态表)带查找数据的图像表的工作表

我有一个带有3个textbox的userform，其中用户将提供textbox 1& textbox2值，如果在查找列c中找到，那么textbox3必须从上面的sheet 用户形象从col返回vlookup值。

查找表行是动态的，但列是固定的，因此我编写了以下代码以获取范围、textbox1和textbox2值，然后将其传递给函数以获得textbox 3中的值

但代码不起作用，我也不知道如何将textbox中的2个条件传递给vlookup &第三个框中的返回值

代码

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

端函数

Answer 1

这可能不是最好的解决方案，但它会给你你正在寻找的结果。

在“查找”表中创建一个辅助列，连接A列和B列。

然后使用Textbox1和Textbox2的连接进行查找。