我正在尝试基于现有工作簿创建 Excel 工作表。我无法更改工作簿的格式，所以我坚持以下内容。
我们正在创建一个系统，以使用带有包含员工姓名的条形码的手持扫描仪，以跟踪 COVID 测试。
我们的工作簿有一栏是名字，一栏是姓。
所以 B2 = Cluff，C2 = Aaron
如何使用输入框（用于扫描仪）来搜索字符串“Cluff, Aaron”（用户在框中输入，而不是代码中专门的名称）并返回包含上述数据的行？
如果它打开一个新的输入框来输入测试 UPC（另一个寻找字符串的输入），并输入到同一行的 AA 列，则加分。
我对 VBA 的了解非常有限。
我尝试了对以下代码的各种编辑：
Sub DualFind()
Dim vFind1 As String, vFind2 As String
Dim rFound As Range, lLoop As Long
Dim bFound As Boolean
Dim rLookIn1 As Range, rLookIn2 As Range
vFind1 = InputBox("Find What: First value?", "FIND FIRST VALUE")
If vFind1 = vbNullString Then Exit Sub
vFind2 = InputBox("Find What: Second value?", "FIND SECOND VALUE")
If vFind2 = vbNullString Then Exit Sub
If Selection.Areas.Count > 1 Then
Set rLookIn1 = Selection.Areas(1).Columns(1)
Set rLookIn2 = Selection.Areas(2).Columns(1)
Else
Set rLookIn1 = Selection.Columns(1)
Set rLookIn2 = Selection.Columns(2)
End If
Set rFound = rLookIn1.Cells(1, 1)
For lLoop = 1 To WorksheetFunction.CountIf(rLookIn1, vFind1)
Set rFound = rLookIn1.Find(What:=vFind1, After:=rFound, LookAt:=xlWhole)
If UCase(rLookIn2.Cells(rFound.Row, 1)) = UCase(vFind2) Then
bFound = True
Exit For
End If
Next lLoop
If bFound = True Then
MsgBox "Match found", vbInformation, "ozgrid.com"
Range(rFound, rLookIn2.Cells(rFound.Row, 1)).Select
Else
MsgBox "Sorry, no match found", vbInformation, "ozgrid.com"
End If
End Sub
从我收集的信息来看，它需要两个单独的输入来搜索列。我需要它用一个输入搜索两列。我想您必须将 B 列和 C 列编译成一个字符串，然后根据框中的输入进行搜索。
不完全确定这是否是你所追求的，但希望它至少会给你更多的想法。我写这个是为了让用户以 [first name] [last name] 格式输入搜索名称，例如 Aaron Cluff。我已经根据您所写的内容假设姓氏出现在第 2 列中，而名字出现在第 3 列中。
Sub Demo()
Dim SearchName As String
Dim UPC As String
Dim LastRow As Long
Dim Row As Long
Dim RowMatch As Long
Dim ColFirstName As Integer
Dim ColLastName As Integer
ColLastName = 2
ColFirstName = 3
SearchName = InputBox("Enter search name: e.g. Aaron Cuff", "Search")
If SearchName = "" Then Exit Sub
SearchName = Trim(SearchName)
LastRow = Cells(Rows.Count, ColLastName).End(xlUp).Row
For Row = 1 To LastRow
If StrComp(SearchName, Trim(Cells(Row, ColFirstName)) & " " & Trim(Cells(Row, ColLastName)), vbTextCompare) = 0 Then
RowMatch = Row
Exit For
End If
Next
If RowMatch = 0 Then
MsgBox "Search Name: " & StrConv(SearchName, vbProperCase) & vbNewLine & vbNewLine & _
"No Match Found", vbInformation, "Search Result"
Exit Sub
End If
UPC = InputBox("Enter Test UPC for " & StrConv(SearchName, vbProperCase) & ": ", "Input")
If UPC <> "" Then
Cells(RowMatch, "AA") = UPC
End If
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句