m3mdicl
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.
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.
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
Ok quick question. Will the data be sorted in the flex? esp the Date Column? Ascending or Descending doesn't matter.
Sid
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.
As you can see it's incomplete. There's no clear method there and it lacks 1 loop I think.
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
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
No, I mean will the data in the flex be sorted?
Sid
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.
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
Sid
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just replace
MSHFlexGrid1
by
MSFlexGrid1
in the code above and then run it. I don't think you need to change anything else.
Sid
MSHFlexGrid1
by
MSFlexGrid1
in the code above and then run it. I don't think you need to change anything else.
Sid
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.
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
Before is the project that I tried.
Sid
msgflex.exe
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
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
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
ASKER
It's ok i found a workaround thanks for the help.
The final code looks something like this.
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.
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
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
Sid
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
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
Sid