Counting the number of certain characters in an array in VBA

Question

Hello all so I am very new to VBA but have take some beginner classes in Java and HTML so I Almost know what I am doing.

So I have a cell in which I want to split based on a comma (,) and I want to count the number of occurrences of a certain Character and then display that result in another cell. Doing this while going down a column of these rows in that single column.

ex: y,y,y,n,y = in cell D6 : 4

Below is the bit of code I have generated with the little bit of information I have learned about VBA online.

I have also seen this bit of code but it get very complex very fast (link) so if someone could explain that one instead I would be very grateful.

(I tried to comment on the answer but dont have enough rep to do so)

And please when giving the answer try to explain the methods, I dont have an understanding of what each does, being both new to programming and VBA so in regaurds to your replies see if I am able to figure it out with a bit of a push.

    Private Sub CommandButton15_Click()

Dim Number As String
Dim Yoccur As Integer
Dim Noccur As Integer
Dim Notapp As Integer


Dim length As Integer
Dim current As String


Dim i As Integer


Dim Row As Integer

Do While Row < 84
For i = 1 To length


'parse data into a array here

'tempArr = Split(X(lngRow, 2), ",")
' would that work if I tried to split based on the comma?


If current = "y" Then Yoccur = Yoccur + 1
If current = "n" Then Noccur = Noccur + 1
If current = "n/a" Then Notapp = Notapp + 1

Next i

Wend


Range("d45").Value = Yoccur
Range("d46").Value = Noccur
Range("d47").Value = Notapp


End Sub

Answer 1

The following code should help. Note the assumptions:

• Your input is located in column A of the sheet, across multiple rows with no spaces between them and starts at row 1.
• The output column for "y" is D, for "n" is E and for "n/a" is F.

Private Sub CommandButton15_Click()

  Dim lastRow As Long
  Dim row As Long
  Dim c As Range
  Dim arr() As String
  Dim s As Variant
  Dim sumY As Long
  Dim sumN As Long
  Dim sumNA As Long

  ' This is the last row of your input in column A.
  lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).row

  For row = 1 To lastRow ' Loop all used rows
    sumY = 0 ' Reset for every loop.
    sumN = 0
    sumNA = 0

    Set c = Cells(row, 1)

    arr = Split(c, ",") ' Split the value in the cell (row, 1) on comma.

    ' For each string in the split array, do lower-case comparisons
    ' for "y", "n" and "n/a" and increment appropriate counters.
    For Each s In arr
      If LCase(s) = "y" Then
        sumY = sumY + 1
      ElseIf LCase(s) = "n" Then
        sumN = sumN + 1
      ElseIf LCase(s) = "n/a" Then
        sumNA = sumNA + 1
      Else
        MsgBox "Unknown value!" ' Sanity test if something is wrong with the input.
      End If
    Next

    ' Assign the sums of "y", "n" and "n/a" to columns 4, 5 and 6 (D, E and F).
    Cells(row, 4).Value = sumY
    Cells(row, 5).Value = sumN
    Cells(row, 6).Value = sumNA
  Next row

End Sub

I'm sure it's not "perfect" (it's very late -- or early -- here!) but it should work and it should at least give you some ideas on how to proceed from here.

Let me know if you need me to explain anything that's not clear.

---

UPDATE

Given the same assumptions as above (you can change column A to column C by replacing 1 to 3 in the column indexing of the code) and your comment about linebreaks if I understood it correctly, the following code will now not only calculate the values for each row but will instead count the occurrences of "y", "n" and "n/a" across all rows and will put the results in cell D1 for "y", E1 for "n" and F1 for "n/a".

Private Sub CommandButton15_Click()

  Dim lastRow As Long
  Dim row As Long
  Dim c As Range
  Dim arr() As String
  Dim s As Variant
  Dim sumY As Long
  Dim sumN As Long
  Dim sumNA As Long

  ' This is the last row of your input in column A.
  lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).row

  For row = 1 To lastRow ' Loop all used rows
    Set c = Cells(row, 1)

    arr = Split(c, ",") ' Split the value in the cell (row, 1) on comma.

    ' For each string in the split array, do lower-case comparisons
    ' for "y", "n" and "n/a" and increment appropriate counters.
    For Each s In arr
      If LCase(s) = "y" Then
        sumY = sumY + 1
      ElseIf LCase(s) = "n" Then
        sumN = sumN + 1
      ElseIf LCase(s) = "n/a" Then
        sumNA = sumNA + 1
      Else
        MsgBox "Unknown value!" ' Sanity test if something is wrong with the input.
      End If
    Next
  Next row

  ' Assign the sums of "y", "n" and "n/a" to columns 4, 5 and 6 (D, E and F).
  Cells(1, 4).Value = sumY
  Cells(1, 5).Value = sumN
  Cells(1, 6).Value = sumNA

End Sub

Answer 2

The code below

1. Sets a range from D6 to the last used cell in column D
2. It joins all the values in this range together into a single string (strV) using Join

3. It dumps the number of y values in A1 by subtracting the length of the string with all y characters removed from the length of the unaltered string (more detailed explanation below)

   • [a1] = Len(strV) - Len(Replace(strV, "y", vbNullString)) puts the number of y values in A1
   • [a3] = (Len(strV) - Len(Replace(strV, "n/a", vbNullString))) / 3 puts the number of n/a values in A2 (the /3 is needed to count removing the 3 character length n/a as one replacement)
   • [a2].Value = Len(strV) - Len(Replace(strV, "n", vbNullString)) - [a3].Value] counts the number of n values - the number of n/a values in A3 (as n/a contains a n)

code

Sub QuickDump()
Dim rng1 As Range
Dim strV As String
Set rng1 = Range([d6], Cells(Rows.Count, "d").End(xlUp))
strV = Join(Application.Transpose(rng1.Value), ",")
d = Filter(Application.Transpose(rng1.Value), "y", True, vbTextCompare)
[a1] = Len(strV) - Len(Replace(strV, "y", vbNullString))
[a3] = (Len(strV) - Len(Replace(strV, "n/a", vbNullString))) / 3
[a2].Value = Len(strV) - Len(Replace(strV, "n", vbNullString)) - [a3].Value
End Sub