检测格式为文本的重复项

本文介绍了检测格式为文本的重复项的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要一个功能来检测格式为文本的重复项.

I need a function to detect duplicates formatted as text.

这不能区分"46.500"和"46.5000". CountIf可能会将单元格作为数字进行比较.这些单元格被格式化为文本.我试图在数字前加撇号.

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.

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

有人知道如何强制CountIf将单元格作为字符串进行比较或以其他方式检查VBA中的重复项吗?

Does anyone know how to force CountIf to compare cells as strings or other way to check for duplicates in VBA?

推荐答案

在这种情况下，我通常会发现ado很有用.

I usually find ado useful in such circumstances.

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