Excel 用VBA查询重复值
发布网友
发布时间：2022-04-23 06:49
共1个回答
热心网友
时间：2022-06-17 00:39
Private Sub CommandButton1_Click()
Dim BmCell As Range
If TextBox1.Value = "" Then
MsgBox "请输入所要查询的编码"
Exit Sub
End If
With Worksheets("刀具信息")
textbox11.value = ""
For Each BmCell In .Range("L2:L" & .Range("l65536").End(xlUp).Row)
If CStr(BmCell) = TextBox1.Value Then
'下一行if判断中返回查到相同编码的单元格地址如果有多个单元格地址用","隔开
If textbox11.Value = "" Then
textbox11.Value = BmCell.Address(0, 0)
Else
textbox11.Value = textbox11.Value & "," & BmCell.Address(0, 0)
End If
End If
Next
End With
End Sub追问您写的应该是没有问题，但看不太懂，还希望能帮忙具体点，假设L列有三个相同的编码，将查询结果返回到textbox11，textbox12和textbox13，谢谢～
追答我上面给你的代码是返回所有相同编码的单元格地址在textbox11里.单元格地址用","隔开
你的要求,只能返回查询到的前3个相同编码的单元格地址.
Private Sub CommandButton1_Click()
Dim BmCell As Range
If TextBox1.Value = "" Then
MsgBox "请输入所要查询的编码"
Exit Sub
End If
With Worksheets("刀具信息")
textbox11.value = ""
textbox12.value = ""
textbox13.value = ""
For Each BmCell In .Range("L2:L" & .Range("l65536").End(xlUp).Row)
If CStr(BmCell) = TextBox1.Value Then
'下一行if判断中分别在3个文本框中返回查到前3个相同编码的单元格地址
If textbox11.Value = "" Then
textbox11.Value = bmcell.Address(0, 0)
ElseIf textbox12.Value = "" Then
textbox12.Value = bmcell.Address(0, 0)
ElseIf textbox13.Value = "" Then
textbox13.Value = bmcell.Address(0, 0)
exit sub '没地方返回结果了.所以退出查找过程
End If
End If
Next
End With
End Sub
