Link to home
Start Free TrialLog in
Avatar of m3mdicl
m3mdiclFlag for United States of America

asked on

Sum data in MSFlexgrid.

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.
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Where do you want the output?

Sid
Avatar of m3mdicl

ASKER

I want the output on the same Msflexgrid above.  I want them to clear and replace with the expected output.
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
Avatar of m3mdicl

ASKER

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.
>>>Ascending would be great just as the expected output.

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

Sid
Avatar of m3mdicl

ASKER

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.
m3mdicl: You have misunderstood me. I am asking if the dates are Sorted already?

Sid
Avatar of m3mdicl

ASKER

>>> 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.
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just replace

MSHFlexGrid1

by

MSFlexGrid1

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

Sid
Avatar of m3mdicl

ASKER

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.
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
Avatar of m3mdicl

ASKER

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
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
Avatar of m3mdicl

ASKER

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.
Glad it is sorted :)

Sid
Avatar of m3mdicl

ASKER

@Siddharth

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

Check out this thread.

https://www.experts-exchange.com/questions/27581487/Sum-data-in-MSFlexgrid-Code-Problem.html?anchorAnswerId=37580988#a37580988