![在 Microsoft Excel 2013 中搜索两列并使用特定值设置第三列的值](https://linux22.com/image/1463182/%E5%9C%A8%20Microsoft%20Excel%202013%20%E4%B8%AD%E6%90%9C%E7%B4%A2%E4%B8%A4%E5%88%97%E5%B9%B6%E4%BD%BF%E7%94%A8%E7%89%B9%E5%AE%9A%E5%80%BC%E8%AE%BE%E7%BD%AE%E7%AC%AC%E4%B8%89%E5%88%97%E7%9A%84%E5%80%BC.png)
我想搜索特定文本,例如。firstname_lastname
有John_Smith
一列和另一列。因此,在知道哪一行具有我搜索的名字和姓氏后,我想将同一行中单元格的值设置为特定值。worksheetA
WorksheetA
firstname
lastname
例如,如果这是我的工作表:
Firstname Lastname Found
Matt Damon No
Smith Andrew No
John Smith No
Tom Mark No
我的搜索词John_Smith
是
Firstname Lastname Found
Matt Damon No
Smith Andrew No
John Smith Yes
Tom Mark No
请问有什么建议可以在 VBA Excel 中实现这一点吗?
答案1
该代码应该可以工作:
Public Sub searchfullname()
fullname = InputBox("Input first and last name separated by _")
namesarray = Split(fullname, "_")
i = 2
dataintable = True
result = "No"
m = ActiveSheet.Cells(i, 1)
If m = "" Then dataintable = False
While dataintable = True
result = "No"
If m = namesarray(0) Then
n = ActiveSheet.Cells(i, 2)
If n = namesarray(1) Then
result = "Yes"
End If
End If
ActiveSheet.Cells(i, 3) = result
i = i + 1
m = ActiveSheet.Cells(i, 1)
If m = "" Then dataintable = False
Wend
End Sub