Searching for a cells text in a string

[matt_leonard]

Hi,

I am comparing 2 speadsheets / workbooks to see if the data on one sheets is on the other sheet and highlighting it if it is. The following code is fine for looking to see if the 2 cells match exactly but I now need another loop to go through column F which contains the same text but in the middle of a string in the cells. I need this to be highlighted also. Can anyone help? Thank you

MY_MATCH = 0

For MY_ROWS_1 = 2 To Sheets("Sheet1").Range("A65536").End(xlUp).Row
For MY_ROWS_2 = 2 To Sheets("A").Range("A65536").End(xlUp).Row
If Sheets("Sheet1").Range("F" & MY_ROWS_1).Value = _
Sheets("A").Range("K" & MY_ROWS_2).Value Then
Sheets("A").Range("K" & MY_ROWS_2).EntireRow.Font.ColorIndex = 5
MY_MATCH = 1
End If

Next MY_ROWS_2
MY_MATCH = 0
Next MY_ROWS_1


End Sub

 

[ravishankar]

Hi
try these codes

Sub leonard()
Dim a As Long, b As Long, x As Long
x = Sheets("sheet1").Cells(Rows.Count, 6).End(xlUp).Row
y = Sheets("A").Cells(Rows.Count, 11).End(xlUp).Row
    For a = 2 To x
        For b = 2 To y
            If InStr(Sheets("A").Cells(b, 11), Sheets("sheet1").Cells(a, 6)) > 0 Then
            Sheets("A").Cells(b, 11).Interior.ColorIndex = 5
            End If
        Next b
    Next a
MsgBox "complete"
End Sub

Ravi

 

[Peter_SSs]

Matt

I'd like to know a bit more about your data and requirements. For example, for you existing code:
- Are there any duplicates in 'Sheet1' column F?
- Are there any duplicates in 'A' column K?

For your proposed code:
- Do you mean you want to see if the text in 'Sheet1' column F if contained in any values in 'A' column F? I wasn't sure about what columns on what sheet are being compared for the partial match and also which sheet has the shorter values and which one the longer values.


However, I can make some comments about your existing code.

1. It could definitely be sped up, as you should not need to loop through every cell in both columns. Just how much and how, depends partly on your answers to the questions above.

2. I can see no use for
MY_MATCH = 0
and
MY_MATCH = 1

However, my 'guess' is that you are only expecting at most 1 match for each value checked. Which again comes back to my question about duplicates.

If you don't have a very big data set you won't notice the difference, but you could try this faster code for your original problem (exact match). It assumes there are not duplicates in col K of sheet 'A', or if there are, you only want the first one coloured.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CheckCols1()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, rLookFor <SPAN style="color:#00007F">As</SPAN> Range, rLookIn <SPAN style="color:#00007F">As</SPAN> Range, Found <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>        <SPAN style="color:#00007F">Set</SPAN> rLookFor = .Range("F2", .Range("F" & Rows.Count).End(xlUp))<br>    End <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("A")<br>        <SPAN style="color:#00007F">Set</SPAN> rLookIn = .Range("K2", .Range("K" & Rows.Count).<SPAN style="color:#00007F">End</SPAN>(xlUp))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> rLookFor<br><SPAN style="color:#00007F">Set</SPAN> Found = rLookIn.Find(What:=c.Value, LookIn:=xlValues, Lookat:=xlWhole, _<br>    MatchCase:=False, SearchFormat:=False)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Found.EntireRow.Font.ColorIndex = 5<br>        End <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

If there can be duplicates in 'A' col K and you want all matches coloured, this code would need some changes.

Your second problem could use a similar code. You may need to target different columns in each sheet (refer my questions again) but the basic change would be that in this line

Set Found = rLookIn.Find(What:=c.Value, LookIn:=xlValues, Lookat:=xlWhole, _
    MatchCase:=False, SearchFormat:=False)

you would just change the red part to xlPart

So you may like to have a play with that yourself before posting back.