VB6 - Search and replace function

Hi

I have a form with 3 MSHFlexgrid. Each grid are populated.

On the side, i have  2 combobox, one to select what MSHFlexgrid i want to search in, the second if the header column name.

Then, i have the Search value I want to search for and then, the replace Textbox with what i want to replace the value with.

So far, i was using this below code, but this is only for hard coded MSHFlexgrid and column.

I can i update it to adjust based on the the 2 combobox

search value
Private Sub replace_value_Click()
    Dim i As Long
    Dim LastRow As Long
    Dim Found As Boolean

    If Len(Trim(search.Text)) = 0 Then Exit Sub

    For i = 1 To MSHFlexGrid1.Rows - 1
        If search.Text = Mid(MSHFlexGrid1.TextMatrix(i, 2), 1, Len(Trim(search.Text))) Then
            LastRow = i
            Found = True

            If Found = True Then
                MSHFlexGrid1.TextMatrix(i, 2) = replace.Text
                Exit Sub
            Else

            End If
        End If

    Next
End Sub

Open in new window

VB6-search.zip
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.

aikimarkCommented:
Just add some code that iterates through the first row to find out which column has the text ("testC" in your example).  Set that column into a variable and then replace the "2" in the following expression to your variable name
MSHFlexGrid1.TextMatrix(i, 2)
aikimarkCommented:
Your search code seems overly complicated.  This should accomplish the same thing.
    For i = 1 To MSHFlexGrid1.Rows - 1
        If search.Text = MSHFlexGrid1.TextMatrix(i, 2) Then
            MSHFlexGrid1.TextMatrix(i, 2) = replace.Text
            Exit Sub
        End If

    Next

Open in new window

Wilder1626Author Commented:
Hi

I like you code:
For i = 1 To MSHFlexGrid1.Rows - 1
        If search.Text = MSHFlexGrid1.TextMatrix(i, 2) Then
            MSHFlexGrid1.TextMatrix(i, 2) = replace.Text
            Exit Sub
        End If

    Next

Open in new window

The problem i have is that the MSHFlexhrid is hard coded in the code when i want to use the Combo1_grid_number.text to specify the MSHFlexgrid and also Combo2_col_name to specify the column of the grid.

This is where i have the problem.
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.

Download eBook now!
Martin LissOlder than dirtCommented:
Currently you are loading Combo2col_name with all the column names from the three grids. Would you rather have it show just the vales from the grid that selected in grid_number?
Wilder1626Author Commented:
That would be even better  :)
Martin LissOlder than dirtCommented:
Try this code. there are some other tweaks I'd make but this is a start.

Private Sub Form_Load()
grid_number.AddItem "MSHFlexgrid1"
grid_number.AddItem "MSHFlexgrid2"
grid_number.AddItem "MSHFlexgrid3"

'new
'Combo2col_name.AddItem "testA"
'Combo2col_name.AddItem "testB"
'Combo2col_name.AddItem "testC"
'Combo2col_name.AddItem "testD"
'Combo2col_name.AddItem "testE"
'Combo2col_name.AddItem "testF"
'Combo2col_name.AddItem "testG"
'Combo2col_name.AddItem "testH"
'Combo2col_name.AddItem "testI"



'HEATHER

MSHFlexGrid1.TextMatrix(0, 0) = "testA"
MSHFlexGrid1.TextMatrix(0, 1) = "testB"
MSHFlexGrid1.TextMatrix(0, 2) = "testC"
MSHFlexGrid1.TextMatrix(0, 3) = "testD"
MSHFlexGrid1.TextMatrix(0, 4) = "testE"


MSHFlexGrid2.TextMatrix(0, 0) = "testC"
MSHFlexGrid2.TextMatrix(0, 1) = "testD"
MSHFlexGrid2.TextMatrix(0, 2) = "testE"
MSHFlexGrid2.TextMatrix(0, 3) = "testF"
MSHFlexGrid2.TextMatrix(0, 4) = "testG"


MSHFlexGrid3.TextMatrix(0, 0) = "testE"
MSHFlexGrid3.TextMatrix(0, 1) = "testF"
MSHFlexGrid3.TextMatrix(0, 2) = "testG"
MSHFlexGrid3.TextMatrix(0, 3) = "testH"
MSHFlexGrid3.TextMatrix(0, 4) = "testI"


MSHFlexGrid1.TextMatrix(1, 0) = "q"
MSHFlexGrid1.TextMatrix(1, 1) = "w"
MSHFlexGrid1.TextMatrix(1, 2) = "e"
MSHFlexGrid1.TextMatrix(1, 3) = "r"
MSHFlexGrid1.TextMatrix(1, 4) = "t"


MSHFlexGrid2.TextMatrix(1, 0) = "y"
MSHFlexGrid2.TextMatrix(1, 1) = "u"
MSHFlexGrid2.TextMatrix(1, 2) = "i"
MSHFlexGrid2.TextMatrix(1, 3) = "o"
MSHFlexGrid2.TextMatrix(1, 4) = "p"


MSHFlexGrid3.TextMatrix(1, 0) = "a"
MSHFlexGrid3.TextMatrix(1, 1) = "s"
MSHFlexGrid3.TextMatrix(1, 2) = "d"
MSHFlexGrid3.TextMatrix(1, 3) = "f"
MSHFlexGrid3.TextMatrix(1, 4) = "g"


MSHFlexGrid1.TextMatrix(2, 0) = "z"
MSHFlexGrid1.TextMatrix(2, 1) = "x"
MSHFlexGrid1.TextMatrix(2, 2) = "c"
MSHFlexGrid1.TextMatrix(2, 3) = "v"
MSHFlexGrid1.TextMatrix(2, 4) = "b"


MSHFlexGrid2.TextMatrix(2, 0) = "n"
MSHFlexGrid2.TextMatrix(2, 1) = "m"
MSHFlexGrid2.TextMatrix(2, 2) = "l"
MSHFlexGrid2.TextMatrix(2, 3) = "k"
MSHFlexGrid2.TextMatrix(2, 4) = "j"


MSHFlexGrid3.TextMatrix(2, 0) = "h"
MSHFlexGrid3.TextMatrix(2, 1) = "g"
MSHFlexGrid3.TextMatrix(2, 2) = "f"
MSHFlexGrid3.TextMatrix(2, 3) = "d"
MSHFlexGrid3.TextMatrix(2, 4) = "s"

MSHFlexGrid1.TextMatrix(3, 0) = "q"
MSHFlexGrid1.TextMatrix(3, 1) = "w"
MSHFlexGrid1.TextMatrix(3, 2) = "e"
MSHFlexGrid1.TextMatrix(3, 3) = "r"
MSHFlexGrid1.TextMatrix(3, 4) = "t"


MSHFlexGrid2.TextMatrix(3, 0) = "y"
MSHFlexGrid2.TextMatrix(3, 1) = "u"
MSHFlexGrid2.TextMatrix(3, 2) = "i"
MSHFlexGrid2.TextMatrix(3, 3) = "o"
MSHFlexGrid2.TextMatrix(3, 4) = "p"


MSHFlexGrid3.TextMatrix(3, 0) = "a"
MSHFlexGrid3.TextMatrix(3, 1) = "s"
MSHFlexGrid3.TextMatrix(3, 2) = "d"
MSHFlexGrid3.TextMatrix(3, 3) = "f"
MSHFlexGrid3.TextMatrix(3, 4) = "g"

'new
grid_number.ListIndex = 0
End Sub

'new
Private Sub FillColumnNames(LI As Integer)

Dim grd As MSHFlexGrid
Dim lngCol As Long

Select Case LI
    Case 0
        Set grd = MSHFlexGrid1
    Case 1
        Set grd = MSHFlexGrid2
    Case 2
        Set grd = MSHFlexGrid3
End Select

Combo2col_name.Clear

For lngCol = 0 To grd.Cols - 1
    Combo2col_name.AddItem grd.TextMatrix(0, lngCol)
Next
Combo2col_name.ListIndex = 0
End Sub

Private Sub grid_number_Click()
'new
FillColumnNames grid_number.ListIndex

End Sub

Private Sub replace_value_Click()
'new essentially rewrote
    Dim i As Long
    Dim LastRow As Long
    Dim Found As Boolean
    Dim grd As MSHFlexGrid
    
    If Len(Trim(search.Text)) = 0 Then Exit Sub
    
    Select Case grid_number.ListIndex
        Case 0
            Set grd = MSHFlexGrid1
        Case 1
            Set grd = MSHFlexGrid2
        Case 2
            Set grd = MSHFlexGrid3
        Case Else
            MsgBox "Please choose a " & Label1.Caption & " to search from"
            Exit Sub
    End Select
    
    For i = 1 To grd.Rows - 1
        If search.Text = Mid(grd.TextMatrix(i, Combo2col_name.ListIndex), 1, Len(Trim(search.Text))) Then
            LastRow = i
            Found = True

            If Found = True Then
                grd.TextMatrix(i, Combo2col_name.ListIndex) = replace.Text
                Exit Sub
            Else

            End If
        End If

    Next
    If Not Found Then
        MsgBox "Not found"
    End If
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
aikimarkCommented:
Iterate through the Controls collection on the form until you find a control with the matching name.  Then set an control/object variable = that iteration's control and stop the iteration.  In your code, replace the hard-coded name of the control with the variable name you just set.
aikimarkCommented:
In fact, you could iterate through your controls to populate your combobox.
Wilder1626Author Commented:
This is perfect

Thanks for all your help. It works great.
aikimarkCommented:
You may not even need to iterate the controls in VB6.  You should try referencing the control by name as the index into the
Controls collection.
Example:
Dim ctlSelectedGrid As Control

Set ctlSelectedGrid = Controls(cboGrid_number.Text)

For i = 1 To ctlSelectedGrid.Rows - 1
        If search.Text = ctlSelectedGrid.TextMatrix(i, 2) Then
            ctlSelectedGrid.TextMatrix(i, 2) = replace.Text
            Exit Sub
        End If

    Next

Open in new window

Again, the "2" will be replaced with the selected column number as I mentioned earlier.
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 MVE 2015
              Experts-Exchange Top Expert Visual Basic Classic 2012 to 2015
aikimarkCommented:
:-(
Wilder1626Author Commented:
hummm, aikimark, let me try what you propose in ID: 41468797

Sorry, i may have been too quick to accept. :-(
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.