Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sum data in MSFlexgrid.

Posted on 2011-04-19
17
Medium Priority
?
1,404 Views
Last Modified: 2012-05-11
Hi everyone!!!

I need help in summing these data by date.

msflex.jpg

How do i do this using one MSFlexgrid?  After summing it, the output should only have 3 rows.

The expected output should be like this.

261   |   5986.67   |   $652.9091   |   700.44   |  $84.0528   |   2/1/2011
 51     |   1959.09   |   $201.2143   |   233.55   |  $28.0260   |   2/2/2011
220   |   4379.10   |   $475.3718   |   199.66   |  $23.9592   |   2/3/2011

Any help is greatly appreciated.
0
Comment
Question by:m3mdicl
  • 9
  • 8
17 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35430395
Where do you want the output?

Sid
0
 

Author Comment

by:m3mdicl
ID: 35430415
I want the output on the same Msflexgrid above.  I want them to clear and replace with the expected output.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35430471
Sorry, I will be slow in replying as I am answering question in another zone as well :)

Ok quick question. Will the data be sorted in the flex? esp the Date Column? Ascending or Descending doesn't matter.

Sid
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:m3mdicl
ID: 35430478
Ascending would be great just as the expected output.  I tried coding but I think there's a flaw on my codes loop. Here's what I started.

Private Sub Command1_Click()
Dim lngIndex As Long

For lngIndex = 1 To MSFlexGrid1.Rows - 1
    With MSFlexGrid1
        If .TextMatrix(lngIndex, 6) = .TextMatrix(lngIndex + 1, 6) Then
           .TextMatrix(lngIndex, 1) = CLng(.TextMatrix(lngIndex, 1)) + CLng(.TextMatrix(lngIndex + 1, 1))
           .TextMatrix(lngIndex, 2) = CDbl(.TextMatrix(lngIndex, 2)) + CDbl(.TextMatrix(lngIndex + 1, 2))
           .TextMatrix(lngIndex, 3) = CDbl(.TextMatrix(lngIndex, 3)) + CDbl(.TextMatrix(lngIndex + 1, 3))
           .TextMatrix(lngIndex, 4) = CDbl(.TextMatrix(lngIndex, 4)) + CDbl(.TextMatrix(lngIndex + 1, 4))
           .TextMatrix(lngIndex, 5) = CDbl(.TextMatrix(lngIndex, 5)) + CDbl(.TextMatrix(lngIndex + 1, 5))
           .TextMatrix(lngIndex, 6) = .TextMatrix(lngIndex, 6)
        End If
    End With
Next
End Sub

Open in new window


As you can see it's incomplete. There's no clear method there and it lacks 1 loop I think.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35430483
>>>Ascending would be great just as the expected output.

No, I mean will the data in the flex be sorted?

Sid
0
 

Author Comment

by:m3mdicl
ID: 35430521
No don't sort leave as is.  I just want to sum everything according to date and then populate the summed results on the same msflexgrid or another dummy msflexgrid it doesn't matter.

There will be instances that there's only 1 unique date in the date column, so you won't do any computation on that just copy that whole row including the date.

On my example, there's 4 (2/1/2011), 3 (2/2/2011) and 4 (2/3/2011).  Hope that helps.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35430537
m3mdicl: You have misunderstood me. I am asking if the dates are Sorted already?

Sid
0
 

Author Comment

by:m3mdicl
ID: 35430553
>>> Will the data be sorted in the flex? esp the Date Column? Ascending or Descending doesn't matter.

Yes i misunderstood you.  I though you want to sort it out.  Yes the dates are already sorted.

Thanks for the help.
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35430831
Try this.

Private Sub Command1_Click()
    Dim lngIndex As Long, j As Long, i As Long
    Dim h1() As Double, h2() As Double, h3() As Double, h4() As Double, h5() As Double, h6() As String
    Dim hh1 As Double, hh2 As Double, hh3   As Double, hh4   As Double, hh5   As Double, hh6 As String
    
    lngIndex = 1
    
    Do While lngIndex < MSHFlexGrid1.Rows
        With MSHFlexGrid1
            If Len(Trim(.TextMatrix(lngIndex, 6))) <> 0 Then
                If .TextMatrix(lngIndex, 6) = .TextMatrix(lngIndex + 1, 6) Then
                    hh1 = hh1 + CLng(.TextMatrix(lngIndex, 1)) + CLng(.TextMatrix(lngIndex + 1, 1))
                    hh2 = hh2 + CDbl(.TextMatrix(lngIndex, 2)) + CDbl(.TextMatrix(lngIndex + 1, 2))
                    hh3 = hh3 + CDbl(.TextMatrix(lngIndex, 3)) + CDbl(.TextMatrix(lngIndex + 1, 3))
                    hh4 = hh4 + CDbl(.TextMatrix(lngIndex, 4)) + CDbl(.TextMatrix(lngIndex + 1, 4))
                    hh5 = hh5 + CDbl(.TextMatrix(lngIndex, 5)) + CDbl(.TextMatrix(lngIndex + 1, 5))
                    hh6 = .TextMatrix(lngIndex, 6)
                Else
                    j = j + 1
                    ReDim Preserve h1(j): ReDim Preserve h2(j): ReDim Preserve h3(j)
                    ReDim Preserve h4(j): ReDim Preserve h5(j): ReDim Preserve h6(j)
                    If hh6 = "" Then
                        h1(j) = .TextMatrix(lngIndex, 1): h2(j) = .TextMatrix(lngIndex, 2)
                        h3(j) = .TextMatrix(lngIndex, 3): h4(j) = .TextMatrix(lngIndex, 4)
                        h5(j) = .TextMatrix(lngIndex, 5): h6(j) = .TextMatrix(lngIndex, 6)
                    Else
                        h1(j) = hh1: h2(j) = hh2: h3(j) = hh3
                        h4(j) = hh4: h5(j) = hh5: h6(j) = hh6
                    End If
                    hh1 = 0: hh2 = 0: hh3 = 0: hh4 = 0: hh5 = 0: hh6 = ""
                End If
            End If
        End With
        lngIndex = lngIndex + 1
    Loop
    
    For i = 1 To MSHFlexGrid1.Rows - 1
        MSHFlexGrid1.TextMatrix(i, 1) = "": MSHFlexGrid1.TextMatrix(i, 2) = ""
        MSHFlexGrid1.TextMatrix(i, 3) = "": MSHFlexGrid1.TextMatrix(i, 4) = ""
        MSHFlexGrid1.TextMatrix(i, 5) = "": MSHFlexGrid1.TextMatrix(i, 6) = ""
    Next i
        
    For i = 1 To UBound(h1)
        With MSHFlexGrid1
            .TextMatrix(i, 1) = h1(i): .TextMatrix(i, 2) = h2(i)
            .TextMatrix(i, 3) = h3(i): .TextMatrix(i, 4) = h4(i)
            .TextMatrix(i, 5) = h5(i): .TextMatrix(i, 6) = h6(i)
        End With
    Next i
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35430855
Just replace

MSHFlexGrid1

by

MSFlexGrid1

in the code above and then run it. I don't think you need to change anything else.

Sid
0
 

Author Comment

by:m3mdicl
ID: 35431056
Hi thanks for the reply.

I got an error in this line

 If .TextMatrix(lngIndex, 6) = .TextMatrix(lngIndex + 1, 6) Then

Error 381.  Subscript out of range.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35431180
Hmmm, Strange. I tested it before uploading. Can I see how are you uploading the data?

Before is the project that I tried.

Sid
msgflex.exe
0
 

Author Comment

by:m3mdicl
ID: 35431316
It's exactly like my screenshot.  

I found the bug.  On your form load make your rows to 5, which is exactly the same as mine.  It has no blank rows before applying the code.

  Dim i As Long, j As Long, Rw As Long, Cl As Long
    MSHFlexGrid1.Cols = 7
    MSHFlexGrid1.Rows = 12

make it

 MSHFlexGrid1.Rows = 5
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35431326
Try this

Do While lngIndex < MSHFlexGrid1.Rows

to

Do While lngIndex < MSHFlexGrid1.Rows - 1

I have switched back to Vista (I have vb6 in XP) so I cannot test it.

Sid
0
 

Author Comment

by:m3mdicl
ID: 35431355
It's ok i found a workaround thanks for the help.
The final code looks something like this.

Private Sub Command1_Click()
    Dim lngIndex As Long, j As Long, i As Long
    Dim h1() As Double, h2() As Double, h3() As Double, h4() As Double, h5() As Double, h6() As String
    Dim hh1 As Double, hh2 As Double, hh3   As Double, hh4   As Double, hh5   As Double, hh6 As String
    
    Dim count As Long
    
    MSHFlexGrid1.Rows = MSHFlexGrid1.Rows + 1
    
    lngIndex = 1
    
    Do While lngIndex < MSHFlexGrid1.Rows
        With MSHFlexGrid1
            If Len(Trim(.TextMatrix(lngIndex, 6))) <> 0 Then
                If .TextMatrix(lngIndex, 6) = .TextMatrix(lngIndex + 1, 6) Then
                    hh1 = hh1 + CLng(.TextMatrix(lngIndex, 1)) + CLng(.TextMatrix(lngIndex + 1, 1))
                    hh2 = hh2 + CDbl(.TextMatrix(lngIndex, 2)) + CDbl(.TextMatrix(lngIndex + 1, 2))
                    hh3 = hh3 + CDbl(.TextMatrix(lngIndex, 3)) + CDbl(.TextMatrix(lngIndex + 1, 3))
                    hh4 = hh4 + CDbl(.TextMatrix(lngIndex, 4)) + CDbl(.TextMatrix(lngIndex + 1, 4))
                    hh5 = hh5 + CDbl(.TextMatrix(lngIndex, 5)) + CDbl(.TextMatrix(lngIndex + 1, 5))
                    hh6 = .TextMatrix(lngIndex, 6)
                    count = count + 1
                Else
                    j = j + 1
                    ReDim Preserve h1(j): ReDim Preserve h2(j): ReDim Preserve h3(j)
                    ReDim Preserve h4(j): ReDim Preserve h5(j): ReDim Preserve h6(j)
                    If hh6 = "" Then
                        h1(j) = .TextMatrix(lngIndex, 1): h2(j) = .TextMatrix(lngIndex, 2)
                        h3(j) = .TextMatrix(lngIndex, 3): h4(j) = .TextMatrix(lngIndex, 4)
                        h5(j) = .TextMatrix(lngIndex, 5): h6(j) = .TextMatrix(lngIndex, 6)
                    Else
                        h1(j) = hh1: h2(j) = hh2: h3(j) = hh3
                        h4(j) = hh4: h5(j) = hh5: h6(j) = hh6
                    End If
                    hh1 = 0: hh2 = 0: hh3 = 0: hh4 = 0: hh5 = 0: hh6 = ""
                End If
            End If
        End With
        lngIndex = lngIndex + 1
    Loop
    
    For i = 1 To MSHFlexGrid1.Rows - 1
        MSHFlexGrid1.TextMatrix(i, 1) = "": MSHFlexGrid1.TextMatrix(i, 2) = ""
        MSHFlexGrid1.TextMatrix(i, 3) = "": MSHFlexGrid1.TextMatrix(i, 4) = ""
        MSHFlexGrid1.TextMatrix(i, 5) = "": MSHFlexGrid1.TextMatrix(i, 6) = ""
    Next i
        
    For i = 1 To UBound(h1)
        With MSHFlexGrid1
            .TextMatrix(i, 1) = h1(i): .TextMatrix(i, 2) = h2(i)
            .TextMatrix(i, 3) = h3(i): .TextMatrix(i, 4) = h4(i)
            .TextMatrix(i, 5) = h5(i): .TextMatrix(i, 6) = h6(i)
        End With
    Next i
    
    MSHFlexGrid1.Rows = MSHFlexGrid1.Rows - (count + 1)
End Sub

Open in new window


I added count variable then added 1 row  then afterwards delete 1 row + the count.

It worked like a charm.  Thanks for the help mate.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35431365
Glad it is sorted :)

Sid
0
 

Author Comment

by:m3mdicl
ID: 37580991
@Siddharth

I think there's a bug on you code i just found about it today.

Check out this thread.

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27581487.html#a37580988
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

580 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