Excel 用VBA查询重复值

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

Text不要和Adodc1绑定，这样：

Private Sub Command1_Click()

Adodc1.RecordSource = "select * from 编号 where 编号='" &Text1.Text &"'"

If Adodc1.Recordset.RecordCount >0 Then

MsgBox "记录己存在"

Else

MsgBox "记录不存在"

Adodc1.Recordset.AddNew

Adodc1.Recordset("编号")=Text1.Text

Adodc1.Recordset("其他字段")=Text2.Text

'......

Adodc1.Recordset.Update

End If

End Sub

Private Sub Form_Load()

'这里就不用AddNew了

End Sub

---