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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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
Back
Top