Excel Function works on one sheet but not another

I have written a short function in Excel to find a number in a range in column AZ and return the row number.  If the number is not found in then finds the next number following.  So if the list contains 070, 071, 073 then looking for 070 would find the row for 070 whereas looking for 072 would return the row for 073.  There is a similar function to work in reverse, so if the list contains 070, 071, 073 then looking for 073 would find the row for 073 whereas looking for 072 would return the row for 071.  The function works fine on the worksheet where I created it, but will not return a value when called from a different worksheet.  The function code is:

Function Start_Range(StartNum As String, CC As String)
    Dim Search_String As String
    Dim SearchNum As Integer
   
    SearchNum = Val(StartNum)
    Search_String = Format(CStr(SearchNum), "000") + CC
    Do While IsNumeric(Application.Match(Search_String, Worksheets("RDB1CY").Range("AZ:AZ"), 0)) = False
        SearchNum = SearchNum + 1
        Search_String = Format(CStr(SearchNum), "000") + CC
    Loop
    Start_Range = Application.Match(Search_String, Range("AZ:AZ"), 0)
End Function

When called from Worksheet RDB1CY the function operates as expected.  When it is called from any other worksheets it returns #N/A.

Can anybody point me to where I am going wrong?
Steve BrownTechnical DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe you can change these lines in your code like this....

Either to this....

IsNumeric(Application.Match(Search_String, Worksheets("RDB1CY").Range("AZ:AZ"), 0)) = False

Start_Range = Application.Match(Search_String, Worksheets("RDB1CY").Range("AZ:AZ"), 0)

Open in new window


OR to this....

IsNumeric(Application.Match(Search_String, ActiveSheet.Range("AZ:AZ"), 0)) = False

Start_Range = Application.Match(Search_String, ActiveSheet.Range("AZ:AZ"), 0)

Open in new window

as per your requirement.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve BrownTechnical DirectorAuthor Commented:
Thanks.  I should have looked a bit more carefully at why the code was failing.  On the 1st example you gave me it was the revised Line 3, I hadn't set the return value to reference the worksheet name, hence the error.

Note to self, don't try writing code at 22:00!!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Don't worry it happens sometime with everyone.
Glad you identified the error in your code and you will remember not to repeat the same mistake in your future codes.

Bottom Line: Mistakes are important to learn to do things better in future. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.