String Finding

[CMDRCentral_Max]

Hello,

I have a workbook with two sheets.
Sheet one consists of two columns (A & B), and extends to row 12,088.
Column A is a list of strings in English, Column B is the text in column A but in French.

Sheet two consists of three columns (A, B, C) and extends to row 104,629.
Column A contains the type of content, Column B has the content I need to search within, Column C is the part number related to the content in Column B.

I'm trying to find the best way to search for the string in Sheet1-ColumnA within Sheet2-ColumnB and marking the part number in column C when a match has been found.

 

[awoohaw]

If I understood your question correctly, try this. I did not put on different worksheets, but you should be able to use this as a guide.

Book1
	A	B	C	D	E	F	G
1	ENGLISH	FRENCH	match		CONTENT	CONTENT SEARCH	PART NBR
2	ENGLISH1	FRENCH1	Match			ENGLISH4	PART_NBR127
3	ENGLISH2	FRENCH2	No Match			ENGLISH14	PART_NBR580
4	ENGLISH3	FRENCH3	Match			ENGLISH7	PART_NBR978
5	ENGLISH4	FRENCH4	Match			ENGLISH6	PART_NBR289
6	ENGLISH5	FRENCH5	No Match			ENGLISH12	PART_NBR538
7	ENGLISH6	FRENCH6	Match			ENGLISH8	PART_NBR810
8	ENGLISH7	FRENCH7	Match			ENGLISH3	PART_NBR692
9	ENGLISH8	FRENCH8	Match			ENGLISH1	PART_NBR515
Sheet6

Cell Formulas
Range	Formula
C2:C9	C2	=IF(ISNUMBER(MATCH(A2,$F$2:$F$9,0)),"Match","No Match")

Or if you want to lookup into sheet A, and get an indicator:

Book1
	A	B	C	D	E	F	G	H
12	ENGLISH	FRENCH			CONTENT	CONTENT SEARCH	PART NBR
13	ENGLISH1	FRENCH1				ENGLISH4	PART_NBR127	TRUE
14	ENGLISH2	FRENCH2				ENGLISH14	PART_NBR580	FALSE
15	ENGLISH3	FRENCH3				ENGLISH7	PART_NBR978	TRUE
16	ENGLISH4	FRENCH4				ENGLISH6	PART_NBR289	TRUE
17	ENGLISH5	FRENCH5				ENGLISH12	PART_NBR538	FALSE
18	ENGLISH6	FRENCH6				ENGLISH8	PART_NBR810	TRUE
19	ENGLISH7	FRENCH7				ENGLISH3	PART_NBR692	TRUE
20	ENGLISH8	FRENCH8				ENGLISH1	PART_NBR515	TRUE
Sheet6

Cell Formulas
Range	Formula
H13:H20	H13	=ISNUMBER(MATCH(F13,$A$13:$A$20,0))

 

[abdelfattah]

@CMDRCentral_Max
you can try this code

Sub test()
Dim c As Range, fn As Range
    With Sheets("sheet1") 'change sheet name
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
            Set fn = Sheets("sheet2").Range("B:B").Find(c.Value, , xlValues, xlWhole)'change sheet name
                If Not fn Is Nothing Then
                     fn.Offset(, 1).Interior.Color = RGB(0, 255, 0)
                   End If
Next
End With
End Sub

the code matches column A in sheet1 with column B in sheet2 and highlight by green the part number in column C if it's matched
I hope this help.

 

[CMDRCentral_Max]

@CMDRCentral_Max
you can try this code

Sub test()
Dim c As Range, fn As Range
    With Sheets("sheet1") 'change sheet name
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
            Set fn = Sheets("sheet2").Range("B:B").Find(c.Value, , xlValues, xlWhole)'change sheet name
                If Not fn Is Nothing Then
                     fn.Offset(, 1).Interior.Color = RGB(0, 255, 0)
                   End If
Next
End With
End Sub

the code matches column A in sheet1 with column B in sheet2 and highlight by green the part number in column C if it's matched
I hope this help.

Hello @abdelfattah,

Thank you for this, I've added this to my sheet and updated the sheet names in the vb code. Unfortunately I get a "Type Mismatch" error after the Macro runs for a bit.
Do you by chance have any suggestions?

 

[abdelfattah]

I get a "Type Mismatch" error after the Macro runs for a bit.

I'm not sure , but where is debug line ?