I don't think that this will help.
I think you'll want the UDF you posted to return a string.
Then you can wrap =indirect() around that string and use it as a range.
I'm not sure what your UDF is doing, but maybe:
Option Explicit
Function GetSalaryRange(strTitle As String) As String
Application.Volatile 'see notes later
Dim wsCatLookup As Worksheet
Dim rngTemp As Range
Dim rngSalRng As Range
Dim rngVar As Range
Dim strCategory As Variant
Set wsCatLookup = ThisWorkbook.Sheets("CategoryLookup")
Set rngTemp = wsCatLookup.Range("A4:B19")
Set rngSalRng = wsCatLookup.Range("E4:G19")
Set rngVar = Nothing
strCategory = Application.VLookup(strTitle, rngTemp, 2, True)
If IsError(strCategory) Then
'do what
Else
Set rngVar = rngSalRng.Find(what:=strCategory, LookAt:=xlWhole, _
LookIn:=xlValues)
If rngVar Is Nothing Then
'not found, do what
Else
Set rngVar = rngVar.Offset(0, 1).Resize(1, 2)
End If
End If
If rngVar Is Nothing Then
GetSalaryRange = CVErr(xlErrRef)
Else
GetSalaryRange = rngVar.Address(external:=True)
End If
End Function
Then the formula in the cell would look like:
=PERCENTRANK(indirect(getsalaryrange(...)),D4,3)
========
But you have another problem. Excel will calculate your UDF whenever it sees
the need to. If you change the string you passed, it knows that your UDF is
dependent on that string (strTitle).
But if you change the data on the categorylookup worksheet, excel doesn't know
about it.
You have two choices.
Add
application.volatile
to your UDF.
Then your UDF recalcs whenever excel recalcs. This is usually overkill--most
times the value returned won't be changing. And if you're unlucky, you could be
looking at results that are a step behind--your workbook needs to be
recalculated and your UDF result is old.
A better/safer approach would be to pass everything your function needs.
Option Explicit
Function GetSalaryRange(strTitle As String, _
rngtemp As Range, _
rngSalRng As Range) As String
Dim rngVar As Range
Dim strCategory As Variant
Set rngVar = Nothing
strCategory = Application.VLookup(strTitle, rngtemp, 2, True)
If IsError(strCategory) Then
'do what
Else
Set rngVar = rngSalRng.Find(what:=strCategory, LookAt:=xlWhole, _
LookIn:=xlValues)
If rngVar Is Nothing Then
'not found, do what
Else
Set rngVar = rngVar.Offset(0, 1).Resize(1, 2)
End If
End If
If rngVar Is Nothing Then
GetSalaryRange = CVErr(xlErrRef)
Else
GetSalaryRange = rngVar.Address(external:=True)
End If
End Function
And you'd call it in a cell like:
=getsalaryrange("whatever",categorylookup!$a$4:$b$19,categorylookup!$e$4:$g$19)
So your percentrank formula would become:
=percentrank(getsalaryrange("whatever",categorylookup!$a$4:$b$19,categorylookup!$e$4:$g$19),d4,3)
A couple of ps's:
This line:
Dim rngTemp, rngSalRng, rngTemp1 As Range
declares rngTemp as a range, but rngTemp and rngSalRng are variants.
either:
Dim rngTemp as range, rngSalRng as range, rngTemp1 As Range
or
Dim rngTemp As Range
Dim rngSalRng As Range
Dim rngVar As Range
(and rngtemp1 wasn't used.)
And finally, there's a lot of stuff that might mismatch. I wasn't sure what to
do if something returned an error/something wasn't found, so I just put "do
what" comments in the code.
