Force data type on Excel macro with CountIf

Question

I want to search for duplicates on a selection list with an Excel macro, here is the code:

Sub surligner_doublons()
Dim ma_cellule As Range
Dim plage As Range
Set plage = Selection

For Each ma_cellule In plage
    If Application.WorksheetFunction.CountIf(plage, ma_cellule.Value) > 1 Then
        ma_cellule.Interior.ColorIndex = 46
    End If
Next ma_cellule
End Sub

The selection :

The column is defined as Text in Excel, so it should show me no duplicates, but unfortunately the CountIf tells me that all my rows are duplicates because it compares the selection as numbers. Do you have a solution to force it to read the cells as text? Thank you !

One option is to use `SUMPRODUCT` instead of `COUNTIF`. – BigBen Aug 02 '22 at 15:37 — BigBen, Aug 02 '22 at 15:37

score 1 · Answer 1 · answered Aug 02 '22 at 17:19

Here's an alternative approach:

Sub surligner_doublons()
    Dim ma_cellule As Range, reps, plage As Range
    
    Set plage = Selection
    For Each ma_cellule In plage
        reps = plage.Worksheet.Evaluate("=SUM((" & plage.Address & "=" & ma_cellule.Address & ")*1)")
        'Debug.Print ma_cellule.Address, reps
        ma_cellule.Interior.ColorIndex = IIf(reps > 1, 46, xlNone)
    Next ma_cellule
End Sub

Force data type on Excel macro with CountIf

1 Answers1