We help IT Professionals succeed at work.

Sum data in MSFlexgrid.

m3mdicl
m3mdicl asked
on
Medium Priority
1,660 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.
Comment
Watch Question

Where do you want the output?

Sid

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
>>> 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.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Just replace

MSHFlexGrid1

by

MSFlexGrid1

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

Sid

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

Commented:
@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/Programming/Languages/Visual_Basic/Q_27581487.html#a37580988
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.