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?

    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:


    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!


  • Re: Valid Function - better idea?


    try this


  • 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?

    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,


  • Re: Valid Function - better idea?

    Quote from CallMeJD

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

    That is a setting you can adjust in VBE > Tools > Options "Required Declaration of Variables"

    It essentially forces you to define (using Dim statement) each variable. Most consider it good practice. Option Explicit forces you to do it. It has helped me consierably troubleshoot, especially in the early days of learning VBA.

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    (retired Excel 2003 user, 3.28.2008)

  • Re: Valid Function - better idea?

    Hi JD,

    Just downloaded your workbook and the function ... works!
    (It returns the value 62909225)

    In general UDFs called from worksheet cells (as opposed to being called by a Sub) will fail if they are trying to manipulate the worksheets in any way rather than just returning a value. In earlier versions of Excel (up until 2000 I believe) the use of Find could cause a UDF to fail.

    I'll bet anyone that gets it to work is using a newer version of Excel. Sorry, that's probably not the answer that you wanted.

  • Re: Valid Function - better idea?


    I received an error if I have the sheetname in mixed case, and the macro has it in upper case.

    Try changing
    With Sheets("DATA " & i)
    With Sheets("Data " & i)


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!