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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 10:50:56  来源:igfitidea点击:

Detecting duplicates formatted as text

excelvba

提问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