CountIf Function does not count in Excel VBA

Question

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.

Set Cell = Cell is redundant. The Let keyword is not used in VBA: just remove all occurrences. Please explain what exactly you are trying to count? Your code 'counts' (writes 1 to Counter) if the first cell is equal to 1&, the second cell is equal to 2&, ..., and replaces each occurrence in the cell and BTW making the cell blank (if true). — VBasic2008
– VBasic2008, Commented Jun 4, 2022 at 12:39
@VBasic2008 , I've added a snippet of a column with characters which need to be deleted. — Oleg Vovk
– Oleg Vovk, Commented Jun 4, 2022 at 21:58

VBasic2008 · Accepted Answer · 2022-06-04 22:49:01Z

1

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

answered Jun 4, 2022 at 22:49

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Oleg Vovk Over a year ago

Thank you very much. This code worked. I also learnt new commands and correct code structure. But there are two moments: 1) what if a cell contains two characters &. And I need to delete only the first occurrence. 2) your code counts number of cells, I need to count number of combination "digit & "&" ".

VBasic2008 Over a year ago

Both of your requirements are covered by the code: 1.) InStr(1, cString, Criteria) (it is similar to Excel's FIND or SEARCH functions) returns the position of the first occurrence of Criteria in cString while 2.) If Position > 0 Then covers only cells (cases) where Criteria was actually found.

Oleg Vovk Over a year ago

I see. Thank you. Maybe you can also tell me why my code did not count cases. Counter = Application.WorksheetFunction.CountIf(Cell, i & "&")

Collectives™ on Stack Overflow

CountIf Function does not count in Excel VBA

1 Answer 1

Remove Characters

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Remove Characters

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related