Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA code that counts the number of occurences of a given character in a string

Posted on 2012-09-04
2
Medium Priority
?
576 Views
Last Modified: 2012-09-05
How do I write the last lines of this code so that it returns not the first occurrence of Range(JC) in the string LtrCnt, but rather the number of occurrences of the Range(JC) value in the string LtrCnt?

Thanks,
John

Sub SeatCountsByClass()
Dim CeL1 As String, CeL2 As String, CeL3 As String, CeL4 As String, _
    CeL5 As String, CeL6 As String, CeL7 As String, CeL8 As String, _
    CeL9 As String, CeL10 As String, X As String, Lft As String, Ln As String, _
    JC As String, BC As String, YC As String, YC2 As String, YC3 As String, _
    IfAnd As String, Opr1 As String, Opr2 As String, pCom As String, _
    ClsP As String, str6 As String, str7 As String, LtrStr As String, LtrCnt As String, _
    LftCel As Range, RgtCel As Range, rng As Range, vCel As Range
 
    For Each cel In [DP6:DV6]
        Lft = "LEFT(" & cel.Address(0, 0) & ","
        Ln = "LEN(" & cel.Address(0, 0) & ")-1)"
        X = Evaluate(Lft & Ln)
        IfAnd = "IF(AND("
        Opr1 = ">="
        Opr2 = "<="
        pCom = "),"
        ClsP = ")))))"
            CeL1 = Cells(cel.Row, 81).Address(0)
            CeL2 = Cells(cel.Row, 82).Address(0)
            CeL3 = Cells(cel.Row, 83).Address(0)
            CeL4 = Cells(cel.Row, 84).Address(0)
            CeL5 = Cells(cel.Row, 85).Address(0)
            CeL6 = Cells(cel.Row, 86).Address(0)
            CeL7 = Cells(cel.Row, 87).Address(0)
            CeL8 = Cells(cel.Row, 88).Address(0)
            CeL9 = Cells(cel.Row, 89).Address(0)
            CeL10 = Cells(cel.Row, 90).Address(0)
            JC = Cells(1, 31).Address
            BC = Cells(1, 32).Address
            YC = Cells(1, 33).Address
            Y2C = Cells(1, 34).Address
            Y3C = Cells(1, 35).Address
        str6 = IfAnd & X & Opr1 & CeL1 & "," & X & Opr2 & CeL2 & ")," & JC & _
         "," & IfAnd & X & Opr1 & CeL3 & "," & X & Opr2 & CeL4 & ")," & BC & _
         "," & IfAnd & X & Opr1 & CeL5 & "," & X & Opr2 & CeL6 & ")," & YC & _
         "," & IfAnd & X & Opr1 & CeL7 & "," & X & Opr2 & CeL8 & ")," & Y2C & _
         "," & IfAnd & X & Opr1 & CeL8 & "," & X & Opr2 & CeL10 & ")," & Y3C & ClsP
         
        str7 = Evaluate(str6)
        LtrCnt = LtrCnt & str7
        rCel = cel.Row
    Next cel
    Cells(rCel, 31) = InStr(LtrCnt, Range(JC))
    Cells(rCel, 32) = InStr(LtrCnt, Range(BC))
    Cells(rCel, 33) = InStr(LtrCnt, Range(YC))
    Cells(rCel, 34) = InStr(LtrCnt, Range(Y2C))
    Cells(rCel, 35) = InStr(LtrCnt, Range(Y3C))

Open in new window

0
Comment
Question by:gabrielPennyback
2 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38366485
Try this.
Cells(rCel, 31) =Len(LtrCnt)-Len(Replace(LtrCnt, Range(JC).Value, ""))

Open in new window

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 38370158
Great inmorie, thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question