Criteria Searching

buntykins · Apr 23rd, 2002, 08:21 AM

Hi, I have a sheet that contains a number of words. What I want is to put a formula in that will look down the sheet and if a criteria is met, to return the column number. Can anyone help?

Aladin Akyurek · Apr 23rd, 2002, 09:10 AM

Quote:

On 2002-04-23 07:21, buntykins wrote:

Hi, I have a sheet that contains a number of words. What I want is to put a formula in that will look down the sheet and if a criteria is met, to return the column number. Can anyone help?

Try to be a bit more specific.

Iain Lewis · Apr 23rd, 2002, 09:12 AM

Hi Buntykins,

I can't help but feel a little strange typing that & yuo're not my wife

Anyway - try this :

Sub Macro1()
Range("a1:a10").Select
myran = Selection.Find(What:="lewis", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Row
Range("d1").Value = myran
End Sub

This looks for "Lewis" in A1 to A10 & returns the row number in cell D1

HTH,

Iain

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 23rd, 2002, 08:21 AM	#1
buntykins Board Regular Join Date: Apr 2002 Posts: 76	Hi, I have a sheet that contains a number of words. What I want is to put a formula in that will look down the sheet and if a criteria is met, to return the column number. Can anyone help?

Apr 23rd, 2002, 09:12 AM	#3
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	Hi Buntykins, I can't help but feel a little strange typing that & yuo're not my wife Anyway - try this : Sub Macro1() Range("a1:a10").Select myran = Selection.Find(What:="lewis", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Row Range("d1").Value = myran End Sub This looks for "Lewis" in A1 to A10 & returns the row number in cell D1 HTH, Iain