Relative Range References in VBA

  • I wrote a UDF to return the value from a table. It works fine as long as the table starts in cell A1. When I move it, it doesn't work so well. Is there are way to make the table references relative instead of absolute? here is the code



    The function basically looks up a gpm under a specified slope and returns the pipe diameter.
    [ATTACH=CONFIG]66034[/ATTACH]


    Suggestions?

  • Re: Relative Range References in VBA


    In VB, function would be:


    or, if you want to skip VB, you can just use the built-in functions like so:
    =INDEX(A3:A15,MATCH(gpm,INDEX(B3:G15,,MATCH(slope,B1:G1,0)),-1))&""""
    See attached for how both are used

    Files

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Relative Range References in VBA


    Thanks for your help Luke.


    I originally used the match and index functions to do it but it's easier to fix it when the people I share the file with mess it up in the form of a UDF.


    I checked the code you wrote and the result is same as mine. It no longer works when the table is anywhere other than A1:G15.

  • Re: Relative Range References in VBA


    Sorry about that. Adjusting for table placement:


    On the starred line, I'm not sure which row you actually want returned. May need to remove the "- 1" part if it's getting the wrong line. Depends on if when gpm is between two values in column, does it take the one above, or the one below. Your call.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Relative Range References in VBA


    Sorry mate. Same result. I can't for the life of me figure out why it doesn't want to work anywhere other than A1:G15.


    The values in the table are maximums so it needs to return the pipe ID that is closest but higher than the gpm.

  • Re: Relative Range References in VBA


    Ah ha! The tricky part was this line

    Code
    1. .Range(.Cells(3, slopeColumn), .Cells(15, slopeColumn))


    It was correctly using the Cells relative to the table to get correct address...but then the range interpreted that address as being relative to table. So, table as in A2:D4, and the cells portion wanted to look at D2:D4, then the .Range interpreted that as being 2nd row in the table and caused function to look at D3:D5. I think I got it now!


    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Relative Range References in VBA


    This is kind of overkill and not that big of a deal, but any idea why it's placing a space between the return value and the "?

  • Re: Relative Range References in VBA


    I'd check the actual cells first, make sure there's not a accidental space at the end. There's nothing in the code that would add a space. :(

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."