Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
MSHFlexGrid2.TextMatrix(r1, Form56.MSHFlexGrid1.TextMatrix(r2, 4) = Form55.MSHFlexGrid2.TextMatrix(r1, 39)
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
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
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
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
If InStr(1, Form55.MSHFlexGrid2.TextMatrix(i, 39), Form56.MSHFlexGrid1.TextMatrix(j, 4), vbTextCompare) <> 0 Then
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"
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"
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)=''
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"
i=2 j=2 Form55.MSHFlexGrid2.TextMatrix(i, 39)=' BOL: 00008987989M' Form56.MSHFlexGrid1.TextMatrix(j, 4)='00008987989M'
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"
strSearchFor = Form56.MSHFlexGrid1.TextMatrix(j, 4)
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"
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'
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"
If (Len(SearchFor(j) <> 0)) And (InStr(1, SearchWhat, strSearchFor(j), vbTextCompare) <> 0) Then
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"
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"
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"
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?
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Example:
Open in new window
Of course, you would use your flexgrid references in place of the string literals in my example.
Form55.MSHFlexGrid2.TextMa