我想搜索特定的文本,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中做到这一点?
此代码应工作:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句