Vba for counting commas and adding them

[interner]

I am trying to write a vba script that counts commas that are visible then adds them to a previously defined variable.

Here's what I have been able to come up with so far, however I know that it won't work because none of the cells have just a comma in them....I am trying to attempt to actually add one to the "counter" variable for every comma that is found within the cell of column "O" or column 15.

Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
counter = 0
Last = Cells(Rows.Count, "O").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "O").Value) = "," And Rows(i).EntireRow.Hidden = False_ Then
counter = counter + 1
End If
Next i

MsgBox counter

 

[interner]

I've gotten a little bit farther, my new question should be a little easier to answer.
How do I make it so that x the variable x attempts to cover the entire range of Column "O" rahter then just the particular cell O2?
Sub test()
Last = Cells(Rows.Count, "O").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "O").Value) <> "" And Rows(i).EntireRow.Hidden = False_ Then
x = [Len(O2) - Len(Substitute(O2, ",", ""))]

End If
Next i
MsgBox x
End Sub

 

[Weaver]

before the loop begins, set x to 0

Then instead of

x = [Len(O2) - Len(Substitute(O2, ",", ""))]

use

str = cells(i,"O").value
x = x + Len(str) - Len(Substitute(str, ",", ""))

BTW, why the square brackets?

Also, why step through backwards? You may have seen this used elsewhere, but it's only relevant if you're deleting rows or there may be a condition which might stop you testing the entire range.

HTH

 

[interner]

lol, I do the backwards thing out of habit, and thanks for your help! I got it

Sub test()
Last = Cells(Rows.Count, "O").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "O").Value) <> "" And Rows(i).EntireRow.Hidden = False_ Then
x = x + Len(Cells(i, "O").Value) - Len(Replace(Cells(i, "O").Value, ",", ""))

End If
Next i
MsgBox x
End Sub


One issue that arose consisted of the word substitute being an incompatible term. So I used "Replace" instead of "substitute" and it worked seamlessly. Thanks again Weav.

 

[Weaver]

One issue that arose consisted of the word substitute being an incompatible term. So I used "Replace" instead of "substitute" and it worked seamlessly. Thanks again Weav.

Ah yes, I forgot about that, what with =SUBSTITUTE() being the worksheet equivalent of VB Replace().