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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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
 

Forum statistics

Threads
1,148,032
Messages
5,744,412
Members
423,870
Latest member
ForceofWill

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top