本人写了部分,后面不懂了,请完成后面的
Sheets("sheet1").Activate '表示sheet1处于激活状态
Dim mytel As String
Dim i As Long
Dim rn As Range
With ActiveSheet.UsedRange
mytel = TextBox1.Text '把文本框1的内容给变量mytel
For i = 1 To [A65536].End(xlUp).Row '查询整张表格sheet1
Set rn = Cells.Find(what:="*" & "mytel" & "*", LookAt:=xlPart) 'find的内容
。。。后面的不懂了
Sheets("sheet1").Activate '表示sheet1处于激活状态
Dim mytel As String
Dim i As Long
Dim rn As Range
With ActiveSheet.UsedRange
mytel = TextBox1.Text '把文本框1的内容给变量mytel
For i = 1 To [A65536].End(xlUp).Row '查询整张表格sheet1
Set rn = Cells.Find(what:="*" & "mytel" & "*", LookAt:=xlPart) 'find的内容
If Not rn Is Nothing Then
firstAddress = rn.Address
Do
msgbox rn.Value '显示找到的单元格值
Set rn = .FindNext(rn) '找下一个
Loop While Not rn Is Nothing And rn.Address <> firstAddress '直到一个轮回
End If
追问你好:
放到mxgbox里显示,点查询按钮,如果有重复的,就可以一条一条出现,但放到textbox2里就只能显示一条?怎么解决?
Sheets("sheet1").Activate '表示sheet1处于激活状态
Dim mytel As String
Dim i As Long
Dim rn As Range
Dim strResult As String
With ActiveSheet.UsedRange
mytel = TextBox1.Text '把文本框1的内容给变量mytel
For i = 1 To [A65536].End(xlUp).Row '查询整张表格sheet1
Set rn = Cells.Find(what:="*" & mytel & "*", LookAt:=xlPart) 'find的内容
If Not rn Is Nothing Then
strResult = strResult & "找到匹配项: " & rn.Address & " 值: " & rn.Value & vbCrLf
Exit For
End If
Next i
End With
If Len(strResult) > 0 Then
MsgBox strResult, vbInformation, "查询结果"
Else
MsgBox "未找到匹配项", vbExclamation, "查询结果"
End If
这段代码首先遍历整个工作表,查找包含mytel的所有单元格。然后,将这些结果连接到一个字符串变量strResult中,并在一个消息框中显示这些结果。如果没有找到任何匹配项,代码将显示一个未找到匹配项的消息框。
…………
回复:
为了正确地实现查找功能并将结果添加到strResult字符串中,我们需要进行一些修改。以下是修订后的VBA代码:
Sheets("sheet1").Activate '表示sheet1处于激活状态
Dim mytel As String
Dim i As Long
Dim rn As Range
Dim strResult As String
Dim firstAddress As String
With ActiveSheet.UsedRange
mytel = TextBox1.Text '把文本框1的内容给变量mytel
Set rn = .Cells.Find(what:="*" & mytel & "*", LookAt:=xlPart) 'find的内容
If Not rn Is Nothing Then
firstAddress = rn.Address
Do
strResult = strResult & "找到匹配项: " & rn.Address & " 值: " & rn.Value & vbCrLf
Set rn = .Cells.FindNext(After:=rn)
Loop While Not rn Is Nothing And rn.Address <> firstAddress
End If
End With
If Len(strResult) > 0 Then
MsgBox strResult, vbInformation, "查询结果"
Else
MsgBox "未找到匹配项", vbExclamation, "查询结果"
End If
在这个修改后的代码中,我们在With语句中使用.Cells,这样我们就可以正确地查找包含mytel的所有单元格。我们还将firstAddress变量添加到代码中,以便在查找到所有匹配项后,正确地结束查找循环。这将确保查找过程能够在找到所有匹配项后正确地结束。