作者:乔山办公网日期:
返回目录:excel表格制作
以A1:E4区域为例,代码如下:
Sub tst()
Dim i%, j%, k%
For i = 1 To 4
For j = 0 To 4
For k = 0 To 4
If Cells(i, 1).Offset(0, j).Value = Cells(i, 1).Offset(0, k).Value And j <> k Then Cells(i, 1).Interior.ColorIndex = i + 10
Next
Next
Next
End Sub
或者下面代7a686964616fe4b893e5b19e366码也可以,
Sub tst()
Dim d As Object '定义变量
Dim i%, j%
For i = 1 To [a65536].End(3).Row
Set d = CreateObject("Scripting.Dictionary") '创建数据字典
For j = 1 To Cells(i, 256).End(xlToLeft).Column
If Cells(i, j) <> "" And WorksheetFunction.CountIf(Range("A" & i & ":IV" & i), Cells(i, j)) > 1 And Not d.exists(Cells(i, j).Value) Then
d.Add Cells(i, j).Value, ""
End If
Next j
Rows(i).ClearContents
If d.Count > 0 Then Cells(i, 1).Resize(, d.Count) = d.keys
Set d = Nothing
Next i
End Sub
1、是查找单列重复还是整个区域重复
2、提取内容时重复的算一个还是有几个提几个,比如J4J5都是x,J3是Ax还是Axx
Sub s()
Set d = CreateObject("scripting.dictionary")
c = InputBox("请输入列标:版")
n = Cells(Rows.Count, c).End(3).Row
For i = 1 To n
a = Cells(i, c).Text
If a <> "" Then
If d.exists(a) Then
MsgBox c & "列内容有重复!权"
Exit Sub
Else
d.Add a, ""
End If
End If
Next
MsgBox c & "列内容无重复!"
End Sub
sub xxx()
for i=10 to 17
s="A"
for j=4 to 10
if cells(j,i).interior.colorindex<>3 then
for k=j +1 to 11
if cells(j,i)=cells(k,i) then
if cells(j,i).interior.colorindex<>3 then
cells(j,i).interior.colorindex=3
s=s & cells(j,i)
endif
cells(k,i).interior.colorindex=3
s=s & cells(k,i)
endif
next
endif
next
if s<>"A" then
cells(3,i)=s
endif
next
end sub