日记首页
|
列表
|
添加日记
|
管理登陆
标题：VBA检测是否重复
<div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">四段代码分别：1.检测并标注 2.检测并提示 3.检测并标注及提示 4.检测全部（不对比2列）并标注<hr><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">Sub zz()<br> Dim d, ar<br> Set d = CreateObject("Scripting.Dictionary")<br> ar = [a2].CurrentRegion<br> For i = 1 To UBound(ar)<br> d(ar(i, 1)) = d(ar(i, 1)) + 1<br> Next<br> For i = 2 To UBound(ar) + 1<br> If d(Cells(i, 1).Value) > 1 Then Cells(i, 1).Interior.ColorIndex = 3<br> Next<br>End Sub</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">Sub 检验()<br>Columns("A:A").Font.ColorIndex = 0<br>Dim w, y As Integer<br>For w = 4 To [a65536].End(xlUp).Row<br>y = [a65536].End(xlUp).Row<br> If WorksheetFunction.CountIf(Range("a2:a" & y), Range("a" & w)) >= 2 Then<br> MsgBox "发现重复", 16, "系统提示"<br> Range("a" & w).Font.ColorIndex = 3<br> End If<br>Next<br>End Sub</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">Public Sub AAA()<br>Dim w&<br>For w = 4 To 5000 Step 1<br>If Application.CountIf(Range("A4:A5000"), Range("A" & w)) >= 2 Then<br>MsgBox "发现重复", 16, "系统提示": Application.Goto Range("A" & w): Exit Sub<br>End If<br>Next<br>End Sub</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br>Sub 检测列重复()</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""> Set d = CreateObject("Scripting.Dictionary")</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""> lr = [b65536].End(3).Row</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""> arr = Range("A1:H" & lr) '范围（小了报错，下标越界）</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""> For i = 1 To UBound(arr)</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""> s = arr(i, 1) & arr(i, 3) & arr(i, 4) & arr(i, 5) & arr(i, 6) & arr(i, 7) & arr(i, 8) '主范围</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""> d(s) = d(s) + 1</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""> Next</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""> For i = 2 To UBound(arr)</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""> s = arr(i, 1) & arr(i, 3) & arr(i, 4) & arr(i, 5) & arr(i, 6) & arr(i, 7) & arr(i, 8)</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""> If d(s) > 1 Then Cells(i, 1).Interior.ColorIndex = 3</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""> Next</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;="">End Sub</div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""><br></div><div title="点击运行该代码！" class="HtmlCode" style="cursor: pointer;" onclick="preWin=window.open('','','');preWin.document.open();preWin.document.write(this.innerText);preWin.document.close();" ;=""></div></div>
