VB6 - Transfer from MSHFlexgrid to another

Hi,
I need your help for what I would like to do bellow.

In my MSHFlexgrid1, I may have a huge volume of rows and columns. But in column 9, I have IDs.
What I need to do is to transfer in MSHFlexxrid3 all rows and columns. But every time a value will show in column 9 that match column 0 from MSHFlexgrid2, when it will transfer into MSHFlexgrid3, it needs to change the value from column 9 only with the SUB_ID in column 1 from MSHFlexgrid2. All the other details must stay the same. So far, this is not a problem.

Where it gets more complicated is if a single value from column 9 may show more than once in column 0 from MSHFlexgrid2. When that happens, it must be clone into a new row so that both SUB_ID are transfer in MSHFlexgrid3. If I have 3 match, then it should clone it 3 times with each SUB_IDs.

Example:
transfert_no1
The value 12 shows twice in MSHFlexgrid2 with 2 different SUB_IDs. So it needs to create a record of each like below:
transfer_no2
ow can i do that?

Thank you for your help.
LVL 11
Wilder1626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please supply a sample project.
Wilder1626Author Commented:
Hi MartinLiss

Sorry. Forgot to put it the fist time.
Test-duplicate.zip
Martin LissOlder than dirtCommented:
I'm at a play and I'll look at it later this evening.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Martin LissOlder than dirtCommented:
Private Sub cmdConvert_Click()
Dim lngRow1 As Long
Dim lngRow2 As Long
Dim lngRow3 As Long
Dim lngCol As Long
Dim bGrid3Match As Boolean

MSHFlexGrid3.Cols = MSHFlexGrid1.Cols
' Copy the headers
For lngCol = 0 To MSHFlexGrid1.Cols - 1
    MSHFlexGrid3.TextMatrix(0, lngCol) = MSHFlexGrid1.TextMatrix(0, lngCol)
Next

For lngRow1 = 1 To MSHFlexGrid1.Rows - 1
    bGrid3Match = False
    lngRow3 = lngRow3 + 1
    ' First copy all the data from grid1 to grid 3
    If MSHFlexGrid1.TextMatrix(lngRow1, 9) <> "" Then
        If MSHFlexGrid3.Rows = lngRow3 Then
            MSHFlexGrid3.Rows = MSHFlexGrid3.Rows + 1
        End If
        For lngCol = 0 To MSHFlexGrid1.Cols - 1
            MSHFlexGrid3.TextMatrix(lngRow3, lngCol) = MSHFlexGrid1.TextMatrix(lngRow1, lngCol)
        Next
    End If
    For lngRow2 = 1 To MSHFlexGrid2.Rows - 1
        If MSHFlexGrid1.TextMatrix(lngRow1, 9) = MSHFlexGrid2.TextMatrix(lngRow2, 0) Then
            If bGrid3Match Then
                ' copy grid 1 to 3 again in a new row
                lngRow3 = lngRow3 + 1
                If MSHFlexGrid1.TextMatrix(lngRow1, 9) <> "" Then
                    If MSHFlexGrid3.Rows = lngRow3 Then
                        MSHFlexGrid3.Rows = MSHFlexGrid3.Rows + 1
                    End If
                    For lngCol = 0 To MSHFlexGrid1.Cols - 1
                        MSHFlexGrid3.TextMatrix(lngRow3, lngCol) = MSHFlexGrid1.TextMatrix(lngRow1, lngCol)
                    Next
                End If
            End If
            ' There's a match bewtween grid 1 and grid 2 so copy the col 9 value from grid 2 to grid 3
            MSHFlexGrid3.TextMatrix(lngRow3, 9) = MSHFlexGrid2.TextMatrix(lngRow2, 1)
            bGrid3Match = True
        End If
    Next
Next
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
That's exactly what i needed. Thanks again for your help.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.

Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.