Help with UDF to find address location and aditional value

[rjh]

Hi Guys, I am having problems figuring out how to use a UDF that I found on another site. it was written by Allen Wyatt.

Function GetAddr(rng As Range) As String
    Dim dMin As Double
    Dim lIndex As Long
    Dim sAddress As String

    With Application.WorksheetFunction
        dMin = .Min(rng)
        lIndex = .Match(dMin, rng, 0)
    End With
    GetAddr = rng.Cells(lIndex).Address
End Function

when I put the formula "=getAddr(AI52:AI112)" in the cell
it returns the correct cell location "$AI$52"

I have 2 issues
1) How do I make the formula use a value form 2 different cells like "=("AI" & AN7 & ":" & "AI" & AN8)"

so that the range is a variable.

2) I actually want the UDF to return the value that is in the example above in cell AJ52

a example of my data is:
280.3 29
280.3 29
280.3 18
280.1 19
280.2 29
280.4 13
280.4 13

I want it to return the "19" in the 4th row.

Thanks
Rick

 

[footoo]

Function GetV(col$, fr As Range, lr As Range) As String
Dim lIndex As Long
Dim rng As Range
Set rng = Range(col & fr & ":" & col & lr)
With Application.WorksheetFunction
    lIndex = .Match(.Min(rng), rng, 0)
End With
GetV = rng(lIndex, 2)
End Function

Enter in the worksheet : =GetV("AI",AN7,AN8)

 

[rjh]

Footoo, Thanks so much! It works great!

Rick