Solved

VB6- Get counts of distinct value in MSHFlexgrid2

Posted on 2012-12-21

Visual Basic Classic

1 solution

349 Views

Last Modified: 2012-12-21

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

Comment

Question by:Wilder1626

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

4 Comments

LVL 11

Author Comment

by:Wilder1626

ID: 387133702012-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

Accepted Solution

by:shorvath

shorvath earned 500 total points

ID: 387140332012-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

Open in new window

LVL 11

Author Comment

by:Wilder1626

ID: 387141202012-12-21

Hello

I just tried but i have about 70 1 = 0 records

LVL 11

Author Closing Comment

by:Wilder1626

ID: 387144032012-12-21

Sorry i did something wrong.

it work very good.

Thanks again

Featured Post

Question has a verified solution.

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

Simple Games

Article by: Martin

Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games. The game allows for a choice of who goes first and keeps track of the number of wins for…

Visual Basic Classic

A Guide to Writing Understandable and Maintainable VBA Code

Article by: Martin

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.

Using Automation to Control Excel from Access

Video by: TechMommy

Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

Basic Error Handling code for VBA and Microsoft Office

Video by: crystal

This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…