matching text strings

Tarheel

Board Regular
Joined
Jul 30, 2002
Messages
158
I have two sheets, sheet1 & sheet2. Cell A6 on sheet1 is my input cell. I want to search column C of sheet2 to try and match the text string that is entered in cell A6 on sheet1. If the text string is found on sheet2, I need the row number it was found in returned.

For instance, if I type MICHAEL JORDAN into cell A6 on sheet1, I want the macro to search column C of sheet2 and if it is found on row 44, I want 44 returned to cell A7 on sheet1.

Thanks!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
DATA_STRING = SHEETS("SHEET1").RANGE("C6")
DATA - SHEETS("SHEET2").RANGE("C1:C500")
CURRENT_ROW = 1

FOR EACH DATALINE IN DATA

IF DATALINE = DATA_STRING THEN

SHEETS("SHEET1").RANGE("D6") = "MATCH FOUND IN C" & CURRENTROW

END IF

CURRENT_ROW = CURRENT_ROW + 1

NEXT I

You would probably make this a double loop so as to loop through all the cells in Column C sheet 1 - in which case the C6/D6 references would obviously become variables.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
DATA_STRING = SHEETS("SHEET1").RANGE("C6")
DATA - SHEETS("SHEET2").RANGE("C1:C500")
CURRENT_ROW = 1

FOR EACH DATALINE IN DATA

IF DATALINE = DATA_STRING THEN

SHEETS("SHEET1").RANGE("D6") = "MATCH FOUND IN C" & CURRENTROW

END IF

CURRENT_ROW = CURRENT_ROW + 1

NEXT I

You would probably make this a double loop so as to loop through all the cells in Column C sheet 1 - in which case the C6/D6 references would obviously become variables.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
DATA_STRING = SHEETS("SHEET1").RANGE("C6")
DATA - SHEETS("SHEET2").RANGE("C1:C500")
CURRENT_ROW = 1

FOR EACH DATALINE IN DATA

IF DATALINE = DATA_STRING THEN

SHEETS("SHEET1").RANGE("D6") = "MATCH FOUND IN C" & CURRENTROW

END IF

CURRENT_ROW = CURRENT_ROW + 1

NEXT I

You would probably make this a double loop so as to loop through all the cells in Column C sheet 1 - in which case the C6/D6 references would obviously become variables.
 

Tarheel

Board Regular
Joined
Jul 30, 2002
Messages
158

ADVERTISEMENT

Thanks for the reply, but there is one problem. The string in column C of sheet 2 will not match exactly, so you cannot compare them as = or <>. If I search for the string MICHAEL JORDAN, cell C44 on sheet 2 may contain the string MICHAEL JORDAN's WORLD CLASS RESTAURANT. I want the search to return a match if the string is found anywhere within a string in the search column.

Thanks.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
you will need to use an instr function...don't have an example to hand...sorry. I am sure someone else could write one of the top of their head though...
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Try: -

Code:
Dim c As Range
Dim myString As String

myString = Sheet1.Range("A6")

Set c = Sheet2.Range("C:C").Find(myString, , xlValues, xlPart)
Sheet1.Range("A7") = c.Row
This message was edited by Mudface on 2002-09-12 12:15
 

Watch MrExcel Video

Forum statistics

Threads
1,118,080
Messages
5,570,085
Members
412,310
Latest member
mark884
Top