Excel VBA：如何获取COUNTIF函数不将文本单元格"1.1"并"1.10"为重复项？

Question

我正在使用Excel VBA中的CountiF函数来检查ID号数组的重复项，如下所示：

• 1.0
• 1.1
• 1.2
• 1.3
• 1.4
• 1.5
• 1.6
• 1.7
• 1.8
• 1.9
• 1.10
• 1.11
• 。

使用我使用的代码，"1.1">

和"1.10"都格式化为文本单元格，当我不希望它们显示为重复项时。

以下是我的代码，我想知道如果可能的话，什么调整可以解决这个问题？

谢谢。

'RESET ERROR IN HISTORY
Workbooks("PERSONAL.xlsb").Sheets("History").Range("F2").Value = "No Duplicates"

'SET STARTING CELL
Cells(1, 3).Select

'GET ACTIVE COLUMN
Dim Col As Long
Col = ActiveCell.Column

'ESTABLISH END ROW
Call Get_Last_Row
Dim EndRow As Long
EndRow = ActiveCell.Row
Cells(1, Col).Select

'RUN MAIN LOOP
Dim Cell As Variant
Dim Source As Range
Set Source = Range(Cells(1, Col), Cells(EndRow, Col))
For Each Cell In Source

If Application.WorksheetFunction.CountIf(Source, Cell) > 1 Then
Workbooks("PERSONAL.xlsb").Sheets("History").Range("F2").Value = "Duplicates"
Cell.Interior.ThemeColor = xlThemeColorAccent5
End If

Next

If Workbooks("PERSONAL.xlsb").Sheets("History").Range("F2").Value = "Duplicates" Then
MsgBox "Duplicate Sheet Names Found"
End If

Answer 1

您可以使用Worksheet.Evaluate来运行数组公式，而不是Countif()该公式不会像计算数字一样计算单元格：

Dim dupes As Long, c As Range, ws As Worksheet, col As Long, rng As Range, n
Set ws = ActiveSheet 'or whatever
col = ActiveCell.Column
Set rng = ws.Range(ws.Cells(1, col), ws.Cells(Rows.Count, col).End(xlUp))

For Each c In rng.Cells
'this will not count values as numbers...
n = ws.Evaluate("=SUM(1*(" & rng.Address & "=" & c.Address & "))")
If n > 1 Then
dupes = dupes + 1
c.Interior.ThemeColor = xlThemeColorAccent5
Else
c.Interior.ColorIndex = xlNone
End If
Next

Workbooks("PERSONAL.xlsb").Sheets("History").Range("F2").Value = _
IIf(dupes > 0, "Duplicates", "No Duplicates")

If dupes > 0 Then MsgBox "Duplicate Sheet Names Found"