vba 检测格式为文本的重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1492048/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Detecting duplicates formatted as text
提问by Plasmuska
I need a function to detect duplicates formatted as text.
我需要一个函数来检测格式为文本的重复项。
This cannot distinguish between "46.500" and"46.5000". CountIf probably compares cells as numbers. These cells are formatted as text. I tried to add an apostrophe prior the numbers.
这无法区分“46.500”和“46.5000”。CountIf 可能将单元格作为数字进行比较。这些单元格被格式化为文本。我试图在数字之前添加一个撇号。
Function check_duplicates(column As String)
LastRow = Range(column & "65536").End(xlUp).row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range(column & "1:" & column & LastRow), Range(column & x).Text) > 1 Then
check_duplicates = x ' return row with a duplicate
x = 1
Else
check_duplicates = 0
End If
Next x
End Function
Does anyone know how to force CountIf to compare cells as strings or other way to check for duplicates in VBA?
有谁知道如何强制 CountIf 将单元格作为字符串进行比较或以其他方式检查 VBA 中的重复项？
回答by Fionnuala
I usually find ado useful in such circumstances.
在这种情况下，我通常会发现 ado 很有用。
Dim cn As Object
Dim rs As Object
strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT F2, Count(F2) AS CountF2 FROM [Sheet1$] " _
& "GROUP BY F2 HAVING Count(F2)>1 "
rs.Open strSQL, cn
s = rs.GetString
MsgBox s
'' Or
Sheets("Sheet2").Cells(2, 1).CopyFromRecordset rs
回答by Lunatik
Assuming all the "text" cells are textual representations of numbers, then the following change will work:
假设所有“文本”单元格都是数字的文本表示，那么以下更改将起作用：
Function check_duplicates(column As String)
Dim lastrow As Long
Dim x As Long
lastrow = Range(column & "65536").End(xlUp).Row
For x = lastrow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range(column & "1:" & column & lastrow), Val(Range(column & x).Text)) > 1 Then
check_duplicates = x ' return row with a duplicate
x = 1
Else
check_duplicates = 0
End If
Next x
End Function
It coerces the value of the criteria cell to a value by the use of the Valfunction
它通过使用Val函数将条件单元格的值强制为一个值
回答by ashleedawg
There are several ways to check for duplicates using VBA - but in case a worksheet formula would help someone (and maybe seemed difficult?), here's an array formula that will notify you whether or not the cells in a given range are unique.
有几种方法可以使用 VBA 检查重复项 - 但如果工作表公式对某人有帮助（可能看起来很困难？），这里有一个数组公式，它会通知您给定范围内的单元格是否唯一。
=IF(MAX(COUNTIF(B:B0,B3:B100))>1,"List has duplicates","List is unique")
- Since this is a worksheet array formula, instead of hitting Enterto finish entering the formula, you'll need to use Ctrl+ Shift+ Enter. (More info in the links below.)
- 由于这是一个工作表数组公式，而不是击球Enter结束输入公式，你将需要使用Ctrl+ Shift+Enter。（更多信息在下面的链接中。）
This (obviously) looks at range B3:B100. You can change it to whatever, but note that the $exists in one part but not the other. Also, if you use it on large ranges (2000+ cells), it might take a few seconds to update every time you change a cell.
这（显然）着眼于 range B3:B100。您可以将其更改为任何内容，但请注意$存在于一部分而不是另一部分。此外，如果您在大范围（2000 多个单元格）上使用它，则每次更改单元格时可能需要几秒钟进行更新。
Alternatively, you could place and remove the formula programmatically with VBA, using the FormulaArrayproperty of the Rangeobject.
或者，您可以使用对象的FormulaArray属性，通过 VBA 以编程方式放置和删除公式Range。
More info from Microsoft about array formulas hereand hereand limitations here.
回答by Jon Fournier
The CountIf function doesn't takes a formula as its second argument, so the second argument should be:
CountIf 函数不接受公式作为其第二个参数，因此第二个参数应该是：
"=" & Range(column & x).Text
"=" & 范围（列 & x）。文本
回答by Plasmuska
Here is new version based on Remou's code. This one is little more versatile and works with MS Excel 2007.
这是基于 Remou 代码的新版本。这个功能稍微多一点，可与 MS Excel 2007 一起使用。
Function check_duplicates(column As Integer)
' checks for duplicates in a column
' usage: column - numerical (A = 1, B=2 etc...)
' returns: "" - no duplicates, otherwise list of duplicates with numbers of occurrences
Dim cn As Object
Dim rs As Object
strFile = ActiveWorkbook.FullName
strSheet = ActiveWorkbook.ActiveSheet.Name
' connection string for Excel 2007
strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & _
";Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strcon
col = "F" & Trim(Str(column))
strsql = "SELECT " & col & ", Count(" & col & ") AS Count" & col & " FROM [" & strSheet & "$]" & _
"GROUP BY " & col & " HAVING Count(" & col & ")>1 "
rs.Open strsql, cn
If rs.BOF = True And rs.EOF = True Then
check_duplicates = ""
Else
check_duplicates = rs.GetString
End If
End Function
