Valid Function - better idea?

I'm trying to write a function that will act as a vba way to run the vlookup formula over multiple sheets. I'm not married to this method, but need the same result. I'm getting a #VALUE! now, so I'm not so sure that my function is even written correctly - I'm not too familiar. Can someone please look it over and tweak as necessary?

Function Find1(sRng, sLocation As Integer) As Double
Application.Volatile True
    For i = 1 To 9
    Sheets("Data " & i).Select
    Range("A2").Select
    Cells.Find(What:=sRng, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
    Find1 = ActiveCell.Offset(0, sLocation)
    Next i
End Function

In B7 I have "1" I want to search column B on sheets Data 1 through Data 9. I want to then return the value in the column directly to the right of the first "1" found.

I call my function using:

=find1($B$7,1)

I would mostly like to use a function to take advantage of not being forced to "run a macro" or anything like that.

Thanks for all the help - once again!

JD

Re: Valid Function - better idea?

Can't seem to get it to work. It looks good though. I made the following modifications to fine-tune it to my sheet - but all I get is the default "Not Found" . .. . . . any ideas?

Option Explicit
Function Find2(sRng As Range, sLocation As Integer) As Variant
    Dim i As Integer
    Dim FResult As Range
    Application.Volatile True
    For i = 1 To 9
        With Sheets("DATA " & i)
            ActiveSheet.Range("a2").Select
            Set FResult = .UsedRange.Find(What:=sRng.Value, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False)
        End With
        If Not FResult Is Nothing Then
            Find2 = FResult.Offset(0, sLocation).Value
            Exit Function
        End If
    Next i
    Find2 = "Not Found"
End Function

Sheet "Data 3" currently has A22 = 1
and I'm calling the data with =find2($B$7,1) - where B7 = 1

I'm also not familiar with the Option Explicit declaration -

Thanks again,

JD

Re: Valid Function - better idea?

I'm attaching my workbook - in hopes that it might help find a "cure".

Let me know if there are any questions - I think we're close, just not quite there.

THANKS EVERYONE!

JD