Re: Parsing and Counting Matches - My State Table.xls (0/1)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Russ (rparrish_at_starpower.net)
Date: 02/12/05 

Date: Sat, 12 Feb 2005 14:45:25 -0500

I started constructing a little VBA routine to do this for me. It's
just starting to take shape and I have a looping error I have to take
care of but its beginning to do exactly what I whated.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Search for occurance in a Cell of a string found in a specific Cell
' Two letters found in Columns 3 - 10 will be used as the String to
Search for
' in the next cell in the 2nd column. When a match is found, increment
the match counter
' When the end of the the cell is encountered, advance to the next cell.
' Loop will continue until search cell is blank.
' At the end of each search cell put match counter in approprirate row
and column
' Need nested loops to traverse both the Search Cell and the String Cells.

' Define variables

Dim CellString As String 'Cell to search
Dim SearchString As String ' String search for
Dim I As Integer

Dim CellEnd As Integer ' End of Search Cell
Dim CharCnt As Integer ' Character Position in Search Cell
Dim CellRow As Integer ' Row for Search Cell, also used for inner loop
control and row to write resluts
Dim CellCol As Integer ' Column for Search Cell
Dim SearchCol As Integer ' Cell Coloumn for String to Search for, also
for outer loop control and column to write results
Dim SearchRow As Integer ' Cell Row for string to search for
Dim MatchCnt As Integer ' Number of times the Search string is found
in the search cell.
Dim Returncode As Integer

'Initialize variables

CharCnt = 1 'first character of cell to look for match
CellRow = 2 ' Initialize Cell Row
CellCol = 2 'Initialize Cell Column B
SearchRow = 1 'Initialize Search String Row
SearchCol = 3 'Initialize Search String Column C
MatchCnt = 0 ' Initialize Match counter
Returncode = 1 ' varible to capture the position where match was found

' Outer loop controls the column used to get the search string
' Inner loop controls which cell to search for matches

Do Until Len(Cells(CellRow, CellCol)) <= 0
SearchString = "Start"
Do While Len(SearchString) > 0
     SearchString = Cells(SearchRow, SearchCol) ' Get the string to
search for
     CellString = Cells(CellRow, CellCol) 'get the search string
     MsgBox (SearchString)
     MsgBox (CellString)
     If Len(CellString) <= 0 Then
         End
     End If
     Returncode = 1
     Do While Returncode > 0
         MsgBox (CharCnt)
        Returncode = InStr(CharCnt, CellString, SearchString, vbTextCompare)
         If Returncode > 0 Then
             MatchCnt = MatchCnt + 1
             CharCnt = Returncode + 1
         Else
             Cells(CellRow, SearchCol).Value = MatchCnt
             MsgBox (MatchCnt)
         End If
     Loop
     ' Need to get next Search String and reset CellString pointer and
clear matchcnt.
     CharCnt = 1
SearchCol = SearchCol + 1
Loop
CellRow = CellRow + 1
Loop

End Sub

rparrish@starpower.net wrote:
> I have two columns of data. The first column is not important for
> this exercise but the second column contains a series of state
> initials. I need to count the number of times the states initials
> appear in the second column and place the total count in a column
> designated for that state. I have attached a two row sample of what
> the finished sheet should look like. I am not a VBA programmer but
> have a lot of programming experience in other programming languages.
> Can this be accomplished using a series of Excel functions or do I
> need to write a VBA program to do this? Any help with this is greatly
> appreciated.
>
> Russ Parrish
> rparrish@starpower.net
>
>

Relevant Pages

  • Re: Formulas containing hard coded values
    ... Dim R As Range, sdoit As String ... MsgBox "Cell contains hard codes" ... ' Grateful thanks to Rick Rothstein ... Dim Fml As String, LCtext As String ...
    (microsoft.public.excel.programming)
  • Re: Formulas containing hard coded values
    ... Dim R As Range, sdoit As String ... MsgBox "Cell contains hard codes" ... Dim Fml As String, LCtext As String ... Dim UCtext As String, OriginalFormula As String ...
    (microsoft.public.excel.programming)
  • VB, implementing the high score within this code
    ... Private possibleAs String ... Private Function SolvePuzzleAs Boolean ... ' Calculates the possible values for all the cell ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Formulas containing hard coded values
    ... Dim R As Range, sdoit As String ... MsgBox "Cell contains hard codes" ... Dim Fml As String, LCtext As String ... For Each Rng In R.Precedents.Areas ...
    (microsoft.public.excel.programming)
  • Re: Marking points read for coordinates
    ... In "Private Sub GetCoordinates()" comment all the code between ... (ByVal lpClassName As String, ByVal lpWindowName As String) ... ' cell A1 which is the base for all object coordinates on a sheet. ... Dim chtObj As ChartObject ...
    (microsoft.public.excel.programming)