VB6- Get counts of distinct value in MSHFlexgrid2

Hello all

I would like to be able to get the counts of distinct value from my MSHFlexgrid2 in column 4.

ex:
In column 4, i can see values like:
20120101
20110605
20101212
20121019

I would like to have the look at the frist 4 chars from that value and then counts the number of time it shows.

Example of the msgbox:
2010 = 1 Records
2011 = 1 Records
2012 = 2 Records

How can i do that please?

Thanks again for your help

Wilder1626

Asked: 2012-12-21

Advertise Here

Visual Basic Classic

1 Solution

LVL 11

Wilder1626Author Commented: 2012-12-21

I did this for now but this only give me the count of different text

Dim MyCollection As New Collection
Dim k As Integer
For k = 1 To MSHFlexGrid2.Rows - 1

        If MSHFlexGrid2.TextMatrix(k, 4) <> "" Then
            On Error Resume Next
            MyCollection.Add MSHFlexGrid2.TextMatrix(k, 4), MSHFlexGrid2.TextMatrix(k, 4)
            On Error GoTo 0
        End If

    Next k
    MsgBox MyCollection.Count

Open in new window

LVL 9

shorvathCommented: 2012-12-21

this will work,

Dim RecordCount() As String
Dim bFound As Boolean
Dim i As Integer
Dim k As Integer
Dim rc As Integer
Dim strMsg As String

ReDim RecordCount(0 To 0)

'Get Distinct List of years
For k = 1 To MSHFlexGrid2.Rows - 1
   If MSHFlexGrid2.TextMatrix(k, 4) <> "" Then
      On Error Resume Next
         bFound = False
         For i = 0 To UBound(RecordCount())
            If Left(RecordCount(i), 4) = Left(MSHFlexGrid2.TextMatrix(k, 4), 4) Then
               bFound = True
            End If
         Next
         If bFound = False Then
            RecordCount(UBound(RecordCount())) = Left(MSHFlexGrid2.TextMatrix(k, 4), 4) & " = 0 Records"
            ReDim Preserve RecordCount(0 To UBound(RecordCount()) + 1)
         End If
      On Error GoTo 0
   End If
Next k

   
''set Count for each Distinct Year
For i = 0 To UBound(RecordCount()) - 1
   For k = 1 To MSHFlexGrid2.Rows - 1
      If Left(RecordCount(i), 4) = Left(MSHFlexGrid2.TextMatrix(k, 4), 4) Then
         rc = CInt(Replace(Right(RecordCount(i), Len(RecordCount(i)) - InStr(1, RecordCount(i), "= ") - 1), " Records", ""))
         RecordCount(i) = Left(RecordCount(i), 4) & " = " & CStr(rc + 1) & " Records"
      End If
   Next k
Next

For i = 0 To UBound(RecordCount())
   strMsg = strMsg & RecordCount(i) & vbCrLf
Next i

MsgBox strMsg