CountIf Function does not count in Excel VBA-CodePudding

in Every cell in a Selection I need to replace a combination of characters. But before a replacement I want to count every combination. The problem is that CountIf function won't count at all. Though replacement performs.

Here is a code:

Option Explicit

Sub sdfsdf()
Dim rng As Range
Dim i As Integer
Dim Cell As Range
Dim Counter As Variant

'Application.Run "Personal.xlsb!Main"

Set rng = Selection
Set Cell = Cell
Let i = 1

For Each Cell In Selection
Let Counter = Application.WorksheetFunction.CountIf(Cell, i & "&")
Cell.Replace What:=i & "&", Replacement:=""
i = i   1
Next Cell

MsgBox Counter
End Sub

A snippet of a column with combinations to delete. I need to delete 1&, 2&, 3& and so on.

CodePudding user response：

Remove Characters

Sub RemoveChars()

    Const Criteria As String = "&"

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rg As Range
    Set rg = ws.Range("U2", ws.Cells(ws.Rows.Count, "U").End(xlUp))
    ' Or:
    'Set rg = Selection
    
    Dim Cell As Range
    Dim Position As Long
    Dim cCount As Long
    Dim cString As String
    
    For Each Cell In rg.Cells
        cString = CStr(Cell.Value)
        Position = InStr(1, cString, Criteria)
        If Position > 0 Then ' criteria found; replace with chars to the right
            Cell.Value = Mid(cString, Position   1, Len(cString) - Position)
            cCount = cCount   1
        'Else ' criteria not found; do nothing
        End If
    Next Cell

    MsgBox "Cells processed: " & cCount, vbInformation

End Sub