|
1.表1-表7的B列为发票号,D列为订单号(Sheet表的格式统一)
2.以B列为例:目前应用数据有效性=COUNTIF($B:$B,B3)<2,可使B列录入重复数据时提示不能录入。(同一张Sheet表可行)
3.现要求关联表1-表7的B列数据,禁止录入重复数据。如:表7的B列存在发票号“28355351”,当在表1的B列中录入“28355351”时提示禁止录入。
4.D列订单号如上设置。
5.请大神们帮忙定制VBA程序。谢谢~
- Sub demo()
- Dim wb As Workbook, i As Integer, k As Integer, rg As Range
- Set wb = ThisWorkbook
- Set rg = Selection.Offset(-1, 0)
- For i = 1 To wb.Sheets.Count
- If InStr(rg.Address, "B") > 0 And rg.Value <> "" Then
- If wb.Sheets(i).Range("b:b").Find(rg.Value, lookat:=xlWhole) Is Nothing Then
- Else
- k = k + 1
- If k > 1 Then
- MsgBox wb.Sheets(i).Name & "的" & wb.Sheets(i).Range("b:b").Find(rg.Value, lookat:=xlWhole).Address & "重复"
- rg.Value = ""
- End If
- End If
- ElseIf InStr(rg.Address, "D") > 0 And rg.Value <> "" Then
- If wb.Sheets(i).Range("d:d").Find(rg.Value, lookat:=xlWhole) Is Nothing Then
- Else
- k = k + 1
- If k > 1 Then
- MsgBox wb.Sheets(i).Name & "的" & wb.Sheets(i).Range("d:d").Find(rg.Value, lookat:=xlWhole).Address & "重复"
- rg.Value = ""
- End If
- End If
- Else
- End If
- Next i
- End Sub
复制代码- Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
- If Target.Cells.Count = 1 And (Mid(Target.Address, 2, 1) = "B" Or Mid(Target.Address, 2, 1) = "D") And Target.Row > 2 Then
- Call demo
- End If
- End Sub
复制代码做了一个粗劣的代码,请看下效果
|
|