Find value in a range

[wayneryeven]

Hey all
Having some trouble with searching for a value in a range.

On Worksheet 'Sheet1' cell A1 i type a value e.g. 4

On Sheet2 row 1 there are 10 columns of info. Columns C-L contain the values 1 to 10.

i want a macro to pick up the value in Sheet1!A1 and find this in the range A1-L1 on Sheet2. When it matches it, i want to put the focus onto the first cell in the column- in this example it would be set to the column+row1 where it finds the number 4.

What i have thus far is as follows:

Sheets("Sheet1").Select
Filter = Range("A1").Text

Sheets("Sheet2").Select
Set rRng2 = Range("A1:L1")

ColumnNo = WorksheetFunction.Match("Filter", Range("A1:L1"), 0)

End Sub

BUT it doesnt like this, i think its how i have used my variable "Filter" in the match function?

Thanks to everyone for their time and efforts,

 

[PHV]

Replace this:
ColumnNo = WorksheetFunction.Match("Filter", Range("A1:L1"), 0)
with this:
ColumnNo = WorksheetFunction.Match(Filter, Range("A1:L1"), 0)

Anyway you may replece the whole stuff with simply this:
ColumNo = WorksheetFunction.Match(Sheets("Sheet1").Range("A1"), Sheets("Sheet2").Range("A1:L1"), 0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

You must also reference the sheet object...

Filter = Sheets("Sheet1").Range("A1").Text

Set rRng2 = Sheets("Sheet2").Range("A1:L1")

ColumnNo = WorksheetFunction.Match(Filter, rRng2, 0)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}