Posts by PaulCrisp1964

    Hi Roy,


    Thank you, will be sure to put in code tags from here on.


    In its current state, the function does what can be done with an INDEX and MATCH functions within excel.


    What I'm hoping to be able to do it, put in a 'Do Until' Loop so that it goes through the values of v - 0.001, v + 0.001, v - 0.002 and v + 0.002, and stops the loop when Len(Fetch) > 0, but not entirely sure how to go about it.


    Shall I upload an Excel spreadsheet to better explain the input and desired output?


    Cheers,

    Paul

    Hi all,


    I'm trying to develop a custom function.


    I have a lookup table called 'Shapes' that has two columns:


    1) Column A has a discontinuous range of values ranging from 0.000 to 55.000

    2) Column B has a unique alphanumeric value for that particular row


    None of the values between 0.000 to 55.000 in Column 1 differ by more than 0.003.


    So far, I have the following:


    Code
    Function Fetch(v) As String
    
    
    ' v can be any value between 0.000 and 55.000, including values that may not necessarily be in column A of the lookup table
    
    
    Fetch = Application.WorksheetFunction.Index(Worksheets("Shapes").Range("B:B"), Application.WorksheetFunction.Match(v) Worksheets("Shapes").Range("A:A"), 0))
    
    
    End Function

    I'm hoping to be able to tell this function that in instances where the value of 'v' does not match and a '#VALUE!' result is returned to try and search for the value of v + 0.001, and then by v - 0.001, and then by v + 0.002, and then by v - 0.002.


    Is there any way I can modify this function so that a value always results when it is run?


    Any help would be appreciated.


    Cheers,