looping / find

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian - 20 Oct 2007 16:47 GMT

Hi,
I want to run a function from say sheet1 that will be like a value in the
cell. "textme". I want to find this string from another sheet, say sheet2.
When it finds it, return the row that it found this string.

How would I loop through the column of sheet 2 and find the text that was
selected from sheet 1?
Thanks

Reply to this Message

Bob Phillips - 20 Oct 2007 17:53 GMT

=MATCH(A2,Sheet2!A:A,0)

where A2 holds that text.

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi,
> I want to run a function from say sheet1 that will be like a value in the
[quoted text clipped - 5 lines]
> selected from sheet 1?
> Thanks

Reply to this Message

Brian - 20 Oct 2007 18:40 GMT

is this not corret?
i = WorksheetFunction.Match(Sheet1!A57, Sheet2!A1:A3000, 0)
keeps returning an error?

> =MATCH(A2,Sheet2!A:A,0)
>
[quoted text clipped - 9 lines]
>> selected from sheet 1?
>> Thanks

Reply to this Message

Dave Peterson - 20 Oct 2007 20:07 GMT

Dim Res as Variant
res = application.match(worksheets("Sheet1").range("a57").value, _
worksheets("Sheet2").range("a1:A3000"), 0)

if iserror(res) then
'no match found
else
msgbox res 'row in sheet2!A1:a3000 that matched Sheet1!A57
end if

> is this not corret?
> i = WorksheetFunction.Match(Sheet1!A57, Sheet2!A1:A3000, 0)
[quoted text clipped - 21 lines]
> >> selected from sheet 1?
> >> Thanks

Signature

Dave Peterson

Reply to this Message

Don Guillett - 20 Oct 2007 22:18 GMT

Also have a look in the vba help index for FIND

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> is this not corret?
> i = WorksheetFunction.Match(Sheet1!A57, Sheet2!A1:A3000, 0)
[quoted text clipped - 15 lines]
>>> selected from sheet 1?
>>> Thanks

Reply to this Message