Solved

# Find text in both MSHflexgrid

Posted on 2012-08-31
545 Views
Hello all

I'm having an issue on this code where i would like to add 1 small criteria to it.

So the code work just like this but i also want to say that if
``````MSHFlexGrid2.TextMatrix(r1, Form56.MSHFlexGrid1.TextMatrix(r2, 4) = Form55.MSHFlexGrid2.TextMatrix(r1, 39)
``````

The problem is that in Form56.MSHFlexGrid1.TextMatrix(r2, 4) , i could see a text that look like for example 00008987989M
and in my Form55.MSHFlexGrid2.TextMatrix(r1, 39), i can see a phrase but in that phrase i could see the same text ex: 00008987989M. Actually, it could be any where in the cell.

So if for ex:
Form56.MSHFlexGrid1.TextMatrix(r2, 4)  = 00008987989M
Form55.MSHFlexGrid2.TextMatrix(r1, 39) =   BOL: 00008987989M  / 00008985559M
So since we have a match with the text: 00008987989M i would continu with the rest of the code:
`````` Next r2
If bFound Then

Else
Form55.MSHFlexGrid2.Row = r1
Form55.MSHFlexGrid2.Col = 39
Form55.MSHFlexGrid2.CellBackColor = RGB(100, 149, 237)
Form55.MSHFlexGrid2.TextMatrix(r1, 38) = "X"

End If
Next r1
``````

How can i add this criteria?

Thanks again

Full code
``````Dim r1 As Long, r2 As Long, i As Long
Dim Text1 As String
Dim Text2 As String
Dim bFound As Boolean
For r1 = 1 To Form55.MSHFlexGrid2.Rows - 1
bFound = False
For r2 = 1 To Form56.MSHFlexGrid1.Rows - 1
Text1 = UCase(Trim(Form55.MSHFlexGrid2.TextMatrix(r1, 33)))
Text2 = UCase(Trim(MSHFlexGrid1.TextMatrix(r2, 24)))
If Text2 = Text1 Then
bFound = False
Exit For
Else
bFound = True
End If

Next r2
If bFound Then

Else
Form55.MSHFlexGrid2.Row = r1
Form55.MSHFlexGrid2.Col = 39
Form55.MSHFlexGrid2.CellBackColor = RGB(100, 149, 237)
Form55.MSHFlexGrid2.TextMatrix(r1, 38) = "X"

End If
Next r1
``````
0
Question by:Wilder1626

LVL 44

Expert Comment

Use the Instr() function to do your matching
Example:
``````If Instr(1, "BOL: 00008987989M  / 00008985559M", "00008987989M", vbCompareText) <> 0 Then
Debug.Print "Matched"
Else
Debug.Print "Not Matched"
End If
``````

Of course, you would use your flexgrid references in place of the string literals in my example.
Form55.MSHFlexGrid2.TextMatrix(r1, 39)
0

LVL 11

Author Comment

HI

Ok, i have tried like this. No error but it does not select all the one that should have a match.

Let me look at this.

Full code:
``````Dim r1 As Long, r2 As Long, i As Long
Dim Text1 As String
Dim Text2 As String
Dim bFound As Boolean
For r1 = 1 To Form55.MSHFlexGrid2.Rows - 1
bFound = False
For r2 = 1 To Form56.MSHFlexGrid1.Rows - 1
Text1 = UCase(Trim(Form55.MSHFlexGrid2.TextMatrix(r1, 33)))
Text2 = UCase(Trim(MSHFlexGrid1.TextMatrix(r2, 24)))
If Text2 = Text1 And InStr(1, Form55.MSHFlexGrid2.TextMatrix(r1, 39)) <> 0 Then
bFound = False
Exit For
Else
bFound = True
End If

Next r2
If bFound Then

Else
Form55.MSHFlexGrid2.Row = r1
Form55.MSHFlexGrid2.Col = 39
Form55.MSHFlexGrid2.CellBackColor = RGB(100, 149, 237)
Form55.MSHFlexGrid2.TextMatrix(r1, 38) = "X"

End If
Next r1
``````
0

LVL 44

Expert Comment

you are missing a parameter in the Instr() function.  Notice that in my example, there are two strings.  I assumed that you would have two different variables in their place.
0

LVL 11

Author Comment

Oh, you are right.

I have changed the code to make it easier but now, like this it's  matching with everything, even what is not matching.

Am i missing something?

``````Dim i As Integer
Dim j As Integer

For i = 1 To Form55.MSHFlexGrid2.Rows - 1
For j = 1 To Form56.MSHFlexGrid1.Rows - 1
If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), Form56.MSHFlexGrid1.TextMatrix(j, 4)) <> 0 Then

Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
Form55.MSHFlexGrid2.Row = i
Form55.MSHFlexGrid2.Col = 38
Form55.MSHFlexGrid2.CellBackColor = &H80FF&

Else

End If

Next
Next
``````
0

LVL 11

Author Comment

I've done some more google search and now, i did this, but still have the same issue where all match, even if there is no match

``````  If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), Form56.MSHFlexGrid1.TextMatrix(j, 4), vbTextCompare) <> 0 Then
``````

Full code:
``````Dim i As Integer
Dim j As Integer

For i = 1 To Form55.MSHFlexGrid2.Rows - 1
For j = 1 To Form56.MSHFlexGrid1.Rows - 1
If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), Form56.MSHFlexGrid1.TextMatrix(j, 4), vbTextCompare) <> 0 Then

Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
Form55.MSHFlexGrid2.Row = i
Form55.MSHFlexGrid2.Col = 38
Form55.MSHFlexGrid2.CellBackColor = &H80FF&

Else
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "NOT MATCH"

End If

Next
Next

MsgBox "Done"
``````
0

LVL 44

Expert Comment

Please test this version of the code and post the ten lines it produces in the Immediate window.
``````Dim i As Integer
Dim j As Integer

Dim lngCount As Long

For i = 1 To Form55.MSHFlexGrid2.Rows - 1
For j = 1 To Form56.MSHFlexGrid1.Rows - 1

If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), Form56.MSHFlexGrid1.TextMatrix(j, 4), vbTextCompare) <> 0 Then
If lngCount < 10 Then
Debug.Print "i=" & i, "j=" & j, "Form55.MSHFlexGrid2.TextMatrix(i, 39)='" & Form55.MSHFlexGrid2.TextMatrix(i, 39) & "'", "Form56.MSHFlexGrid1.TextMatrix(j, 4)='" & Form56.MSHFlexGrid1.TextMatrix(j, 4) & "'"
lngCount = lngCount + 1
End If
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
Form55.MSHFlexGrid2.Row = i
Form55.MSHFlexGrid2.Col = 38
Form55.MSHFlexGrid2.CellBackColor = &H80FF&

Else
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "NOT MATCH"
End If

Next
Next

MsgBox "Done"
``````
0

LVL 11

Author Comment

all have the MATCH result,  but actually, there is no match accept 1

``````i=1           j=3           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=1           j=4           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=1           j=5           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=1           j=6           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=2           j=1           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
i=2           j=3           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=2           j=4           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=2           j=5           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=2           j=6           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
i=3           j=3           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST1'   Form56.MSHFlexGrid1.TextMatrix(j, 4)=''
``````
0

LVL 11

Author Comment

This is the result in the grid

0

LVL 44

Expert Comment

Instr() has a nasty habit of returning a non-zero value when you are looking for an empty string.  I've added a length check in the following snippet.
``````Dim i As Integer
Dim j As Integer

Dim lngCount As Long

For i = 1 To Form55.MSHFlexGrid2.Rows - 1
For j = 1 To Form56.MSHFlexGrid1.Rows - 1

If (Len(Form56.MSHFlexGrid1.TextMatrix(j, 4)) <> 0) And (InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), Form56.MSHFlexGrid1.TextMatrix(j, 4), vbTextCompare) <> 0) Then
If lngCount < 10 Then
Debug.Print "i=" & i, "j=" & j, "Form55.MSHFlexGrid2.TextMatrix(i, 39)='" & Form55.MSHFlexGrid2.TextMatrix(i, 39) & "'", "Form56.MSHFlexGrid1.TextMatrix(j, 4)='" & Form56.MSHFlexGrid1.TextMatrix(j, 4) & "'"
lngCount = lngCount + 1
End If
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
Form55.MSHFlexGrid2.Row = i
Form55.MSHFlexGrid2.Col = 38
Form55.MSHFlexGrid2.CellBackColor = &H80FF&

Else
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "NOT MATCH"
End If

Next
Next

MsgBox "Done"
``````
0

LVL 11

Author Comment

Oh, i think it's working
``````i=2           j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
``````

Let me try with more data.
0

LVL 44

Expert Comment

It is also possible to use the Like operator as well as the Instr() function.
0

LVL 11

Author Comment

humm

I have now tried with about 1000 rows of data, and no match when i should have.

Let me test again

what is the like operator?
0

LVL 11

Author Comment

I think that after there is 1 match, it put all others at no match.
0

LVL 44

Expert Comment

the Like operator is a pattern matching operator in the VB language
0

LVL 11

Author Comment

Ok. Thanks.

Let me test some more a huge file. For now, it should find something but it don't.

I'm just trying to see why.
0

LVL 44

Accepted Solution

From a performance perspective, this version of the search code will be much faster.  It doesn't even start the inner loop if the search criteria is an empty string.
``````Dim i As Integer
Dim j As Integer

Dim strSearchFor As String
Dim lngCount As Long

For j = 1 To Form55.MSHFlexGrid2.Rows - 1
strSearchFor = Form56.MSHFlexGrid1.TextMatrix(j, 4)
If (Len(strSearchFor) <> 0) Then
For i = 1 To Form56.MSHFlexGrid1.Rows - 1
If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), strSearchFor, vbTextCompare) <> 0 Then
If lngCount < 10 Then
Debug.Print "i=" & i, "j=" & j, "Form55.MSHFlexGrid2.TextMatrix(i, 39)='" & Form55.MSHFlexGrid2.TextMatrix(i, 39) & "'", "Form56.MSHFlexGrid1.TextMatrix(j, 4)='" & strSearchFor & "'"
lngCount = lngCount + 1
End If
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
Form55.MSHFlexGrid2.Row = i
Form55.MSHFlexGrid2.Col = 38
Form55.MSHFlexGrid2.CellBackColor = &H80FF&

Else
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "NOT MATCH"
End If

Next i

End If

Next j

MsgBox "Done"
``````
0

LVL 11

Author Comment

Hi

I tried it but i have a run time error 381 subsript out of range on:

``````strSearchFor = Form56.MSHFlexGrid1.TextMatrix(j, 4)
``````
0

LVL 11

Author Comment

I did 2 modification .
``````Dim i As Integer
Dim j As Integer

Dim strSearchFor As String
Dim lngCount As Long

For j = 1 To Form56.MSHFlexGrid1.Rows - 1
strSearchFor = Form56.MSHFlexGrid1.TextMatrix(j, 4)
If (Len(strSearchFor) <> 0) Then
For i = 1 To Form55.MSHFlexGrid2.Rows - 1
If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), strSearchFor, vbTextCompare) <> 0 Then
If lngCount < 10 Then
Debug.Print "i=" & i, "j=" & j, "Form55.MSHFlexGrid2.TextMatrix(i, 39)='" & Form55.MSHFlexGrid2.TextMatrix(i, 39) & "'", "Form56.MSHFlexGrid1.TextMatrix(j, 4)='" & strSearchFor & "'"
lngCount = lngCount + 1
End If
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
Form55.MSHFlexGrid2.Row = i
Form55.MSHFlexGrid2.Col = 38
Form55.MSHFlexGrid2.CellBackColor = &H80FF&

Else
'Form55.MSHFlexGrid2.TextMatrix(i, 38) = "NOT MATCH"
End If

Next i

End If

Next j

MsgBox "Done"
``````

The result i have now is:

``````i=1           j=1           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)='TEST'
i=3           j=1           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST1'   Form56.MSHFlexGrid1.TextMatrix(j, 4)='TEST'
i=40          j=1           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: TEST'    Form56.MSHFlexGrid1.TextMatrix(j, 4)='TEST'
i=2           j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
i=30          j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='   BOL: 00008987989M/456789'   Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
i=31          j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='  BOL: 00008987989M'           Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
i=32          j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='BOL: 00008987989M/ooooo'       Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
i=33          j=2           Form55.MSHFlexGrid2.TextMatrix(i, 39)='    BOL: 00008987989M'         Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
``````
0

LVL 11

Author Closing Comment

Thank you so much for all the help.

It's very fast and all work great.

Thanks again
0

LVL 44

Expert Comment

glad it works.  thanks for the points.
Sorry about the flexgrid name error.

What percentage of your Form56.MSHFlexGrid1 (searchfor) cells were zero-length strings?

Note: You do not need the Else clause.
0

LVL 11

Author Comment

Hi aikimark

Thanks again, i'm doing many many test right now and all looks good so far.

Actually, i don't need the else clause. Just if it MATCH.

What percentage of your Form56.MSHFlexGrid1 (searchfor) cells were zero-length strings?

None. I have value on every row until the last record of the sheet.
0

LVL 44

Expert Comment

how many rows are in your flexgrids?

If a lot, I would suggest copying the Form56.MSHFlexGrid1 column 39 strings (search what) to a string vector (single dimension array) with the same row dimensions (lbound & ubound) as your TexMatrix rows.  Making form and control references can be a performance killer.

My 'performance' tweak doesn't help much if there are aren't any zero-length searchfor strings.

Be sure to add an Exit For just below your MATCH code, unless there can be duplicate matches.

>>Actually, i don't need the else clause. Just if it MATCH.
That's what I wrote.
0

LVL 11

Author Comment

Oh,

The rows will increase with the time.
Well, everyday, it will increase in Form56. Now, i'm at 15000 rows
In form 55, it will vary everyday. Could be less or could be more. an average would be around 58 to  1000.

Now  for the match, i can have more than 1 match. Not to many time but once in a while.
So i guess i should not use the EXIT FOR.
Right?

Do you think i will be good like that?
0

LVL 44

Expert Comment

with that data description, I recommend the following.
* Go back to the original iteration of the Form55 iteration in the outer loop
* Copy the Form56 column into a string array as I described earlier
* Do use an Exit For statement.
It doesn't matter if there are multiple matches in the Form56 column, you are only indicating a match in the Form55 grid, which a first/only instance of a match

Example:
``````Dim i As Long
Dim j As Long

Dim SearchFor() As String
Dim strSearchWhat As String
Dim lngF55Limit As Long
Dim lngF56Limit As Long

lngF55Limit = Form55.MSHFlexGrid2.Rows - 1
lngF56Limit = Form56.MSHFlexGrid2.Rows - 1
Redim SearchFor(1 To lngF56Limit)

For j = 1 To lngF56Limit
SearchFor(j) = Form56.MSHFlexGrid2.TextMatrix(j, 4)
Next

For i = 1 To lngF55Limit
strSearchWhat = Form55.MSHFlexGrid1.TextMatrix(i, 39)
If (Len(strSearchWhat) <> 0) Then
For j = 1 To lngF56Limit
If (Len(strSearchFor(j) <>0) And (InStr(1, SearchWhat, strSearchFor(j), vbTextCompare) <> 0) Then
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
Form55.MSHFlexGrid2.Row = i
Form55.MSHFlexGrid2.Col = 38
Form55.MSHFlexGrid2.CellBackColor = &H80FF&
Exit For
End If

Next j
End If

Next i

MsgBox "Done"
``````

Note: I've changed the data type of your iteration variables to Long.
0

LVL 11

Author Comment

HI,  Ok i have tried this code, but with some modification but i have a compile error:

``````       If (Len(SearchFor(j) <> 0)) And (InStr(1, SearchWhat, strSearchFor(j), vbTextCompare) <> 0) Then
``````

Full code

``````Dim lngF55Limit As Long
Dim lngF56Limit As Long

lngF55Limit = Form55.MSHFlexGrid2.Rows - 1
lngF56Limit = Form56.MSHFlexGrid1.Rows - 1
ReDim SearchFor(1 To lngF56Limit)

For j = 1 To lngF56Limit
SearchFor(j) = Form56.MSHFlexGrid1.TextMatrix(j, 4)
Next

For i = 1 To lngF55Limit
strSearchWhat = Form55.MSHFlexGrid2.TextMatrix(i, 39)
If (Len(strSearchWhat) <> 0) Then
For j = 1 To lngF56Limit
If (Len(SearchFor(j) <> 0)) And (InStr(1, SearchWhat, strSearchFor(j), vbTextCompare) <> 0) Then
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
Form55.MSHFlexGrid2.Row = i
Form55.MSHFlexGrid2.Col = 38
Form55.MSHFlexGrid2.CellBackColor = &H80FF&
Exit For
End If

Next j
End If

Next i

MsgBox "Done"
``````
0

LVL 11

Author Comment

It says variable required with <>.

Strange.
0

LVL 44

Expert Comment

I don't see some of the variables defined in the "Full code"
Always run with explicit variable declaration compile options.

I mixed the variable names for my new string variables; corrected below.
``````Option Explicit

Dim i As Long
Dim j As Long

Dim strSearchFor() As String
Dim strSearchWhat As String
Dim lngF55Limit As Long
Dim lngF56Limit As Long

lngF55Limit = Form55.MSHFlexGrid2.Rows - 1
lngF56Limit = Form56.MSHFlexGrid2.Rows - 1
Redim SearchFor(1 To lngF56Limit)

For j = 1 To lngF56Limit
strSearchFor(j) = Form56.MSHFlexGrid2.TextMatrix(j, 4)
Next

For i = 1 To lngF55Limit
strSearchWhat = Form55.MSHFlexGrid1.TextMatrix(i, 39)
If (Len(strSearchWhat) <> 0) Then
For j = 1 To lngF56Limit
If (Len(strSearchFor(j) <>0) And (InStr(1, strSearchWhat, strSearchFor(j), vbTextCompare) <> 0) Then
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
Form55.MSHFlexGrid2.Row = i
Form55.MSHFlexGrid2.Col = 38
Form55.MSHFlexGrid2.CellBackColor = &H80FF&
Exit For
End If

Next j
End If

Next i

MsgBox "Done"
``````
0

LVL 11

Author Comment

Like this it work, and much more faster also

``````Dim i As Long
Dim j As Long

Dim SearchFor() As String
Dim strSearchWhat As String
Dim lngF55Limit As Long
Dim lngF56Limit As Long

lngF55Limit = Form55.MSHFlexGrid2.Rows - 1
lngF56Limit = Form56.MSHFlexGrid1.Rows - 1
ReDim SearchFor(1 To lngF56Limit)

For j = 1 To lngF56Limit
SearchFor(j) = Form56.MSHFlexGrid1.TextMatrix(j, 4)
Next

For i = 1 To lngF55Limit
strSearchWhat = Form55.MSHFlexGrid2.TextMatrix(i, 39)
If (Len(strSearchWhat) <> 0) Then
For j = 1 To lngF56Limit
If (Len(SearchFor(j)) <> 0) And (InStr(1, strSearchWhat, SearchFor(j), vbTextCompare) <> 0) Then
Form55.MSHFlexGrid2.TextMatrix(i, 38) = "MATCH"
Form55.MSHFlexGrid2.Row = i
Form55.MSHFlexGrid2.Col = 38
Form55.MSHFlexGrid2.CellBackColor = &H80FF&
Exit For
End If

Next j
End If

Next i

MsgBox "Done"
``````
0

LVL 44

Expert Comment

did we just cross-post?
0

LVL 11

Author Comment

lol, i think so.

I really like your last option.

So fast compare to the previous code.

i don't know how to thanks you for all your help.

I did learned a lot with you on this post.
0

LVL 44

Expert Comment

Have you measured the performance difference between the iteration of Form56 in the original code in the inner For loop and the current iteration of the strSearchFor array?

If your Form56 or Form55 column grows significantly, you can consider a different approach to this matching process.  In such a scenario, you would parse the Form55 string to extract the substrings that relate to the BOL numbers.  For each BOL number, you would do a hashed lookup in a dictionary object that contains the unique values from the Form55 list.
0

LVL 11

Author Comment

From what i know, the form 55 would always have between 58 to 1000 max records. 85 % of the time, it would not go higher than 500.

But for the form56, at the beginning of the year, it would start at 1 but at the end of the year, i could have more than 20000 records.

Does it mean that i should search each BOL from Form55 to match the same BOL in form56 than the opposite?
0

LVL 44

Expert Comment

But for the form56, at the beginning of the year, it would start at 1 but at the end of the year, i could have more than 20000 records.

Does it mean that i should search each BOL from Form55 to match the same BOL in form56 than the opposite?

Even though the Form55 list will have more items at the start of the year, you will be better off making Form55 the outer search, since the Form56 list will be an order of magnitude larger by the end of the year.

I think that is how our currently written code snippets process these two lists. The reason why I posted my prior comment was that our use of Instr() for not-exact matching isn't very efficient.

If I applied the "(\d+\w)" regexp pattern to the Form55 items, I could then try to do an exact match on the Form56 items.  If the Form56 items were in a dictionary object, we can do a hashed look-up by invoking the dictionary object's .Exists() method.
Note: the regexp pattern is based on your example
BOL: 00008987989M  / 00008985559M string
"(^|\s)(.{12})(\s|\$)" would also parse your sample string.
I do not have enough samples to recommend a parsing strategy or regexp pattern.

An alternative search technique would be to sort the Form56 list and perform a binary search or build a b-tree.
0

LVL 11

Author Comment

I think we are good now. No need to try to find another solution.

we are in September and i am only at 13000 rows in form56, and it is pretty fast when i match both grid.

Thanks again for all your time and help.
0

## Featured Post

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture. Â  There is also a companion Debug Toolbar that looks like the followinâ€¦
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code thatâ€¦
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). Uâ€¦
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process fromâ€¦