Hi All,

Iam new to Vba coding .I have a foumul
=PERCENTRANK(CategoryLookup!$F$10:$G$10,D4,3) In this formula the firs
parameter ie "CategoryLookup!$F$10:$G$10 " is a range that is to b
dynamcally generated based on some
lookup values.So i tried wriiting a User Defined Fuction(UDF) returnin
that range based on some logic.But it's not working .What can be th
problem.Is it possible to substitute a range parameter in a workshee
function using a UDF returning range.Can anybody help me on this

Thanks in advance
Xcelio

--
xcelio
-----------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...fo&userid=1628
View this thread: http://www.excelforum.com/showthread.php?threadid=31373

This works for me.


In a module:

Function GetRange() As Range

Set GetRange = ActiveSheet.Range("A1:A5")

End Function



In a worksheet formula:
=AVERAGE(GetRange())


Maybe you could post your UDF ?

Tim.


"xcelion" wrote in message
...

Hi All,

Iam new to Vba coding .I have a foumula
=PERCENTRANK(CategoryLookup!$F$10:$G$10,D4,3) In this formula the first
parameter ie "CategoryLookup!$F$10:$G$10 " is a range that is to be
dynamcally generated based on some
lookup values.So i tried wriiting a User Defined Fuction(UDF) returning
that range based on some logic.But it's not working .What can be the
problem.Is it possible to substitute a range parameter in a worksheet
function using a UDF returning range.Can anybody help me on this

Thanks in advance
Xcelion


--
xcelion
------------------------------------------------------------------------
xcelion's Profile:
http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=313732

Hi Tim,

Sorry for the late reply.I was out of station .here is my udf


Function GetSalaryRange(strTitle As String) As Range
Dim wsCatLookup As Worksheet
Dim rngTemp, rngSalRng, rngTemp1 As Range
Dim rngVar As Range
Set wsCatLookup = ThisWorkbook.Sheets("CategoryLookup")
Set rngTemp = wsCatLookup.Range("A4:B19")
Set rngSalRng = wsCatLookup.Range("E4:G19")



'rngTemp.Select

strCategory = WorksheetFunction.VLookup(strTitle, rngTemp, 2
True)

Set rngVar = rngSalRng.Find(what:=strCategory,, LookAt:=xlWhole, _
LookIn:=xlValues).Offset(0, 1).Resize(1, 2)



Set GetSalaryRange = rngVar



End Functio

--
xcelio
-----------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...fo&userid=1628
View this thread: http://www.excelforum.com/showthread.php?threadid=31373

What version of excel are you using?

If it's before xl2002, then excel won't allow you to use .find in a UDF called
from a cell in a worksheet.

(xl2002 allows it. I think others have posted that xl2003 does, too.)

Maybe you could use a couple of application.match() (one for each column you're
searching through).

xcelion wrote:

Hi Tim,

Sorry for the late reply.I was out of station .here is my udf

Function GetSalaryRange(strTitle As String) As Range
Dim wsCatLookup As Worksheet
Dim rngTemp, rngSalRng, rngTemp1 As Range
Dim rngVar As Range
Set wsCatLookup = ThisWorkbook.Sheets("CategoryLookup")
Set rngTemp = wsCatLookup.Range("A4:B19")
Set rngSalRng = wsCatLookup.Range("E4:G19")

'rngTemp.Select

strCategory = WorksheetFunction.VLookup(strTitle, rngTemp, 2,
True)

Set rngVar = rngSalRng.Find(what:=strCategory,, LookAt:=xlWhole, _
LookIn:=xlValues).Offset(0, 1).Resize(1, 2)

Set GetSalaryRange = rngVar

End Function

--
xcelion
------------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=313732

--

Dave Peterson

Hi Dave ,

Iam using xl2002 .I will try using Application.Match().Thanks for yo
help


xcelio

--
xcelio
-----------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...fo&userid=1628
View this thread: http://www.excelforum.com/showthread.php?threadid=31373

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",categoryloo kup!$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.




xcelion wrote:

Hi Dave ,

Iam using xl2002 .I will try using Application.Match().Thanks for you
help

xcelion

--
xcelion
------------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=313732

--

Dave Peterson

Hi Dave,

Thanks a lot for rewriiting the code itself.Iam new to Vba codin
.That's why code look some what less readable.Any way i admire you
coding .Checking for the object every time is the one thing i have t
look after.Please advise me on the good coding tips to prevent progra
crashes .I will try your fuction and get back to you.Thanks a lot

Thanks
Xcelion :

--
xcelio
-----------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...fo&userid=1628
View this thread: http://www.excelforum.com/showthread.php?threadid=31373

I think the most difficult part of any coding is building in the validation
checks. The actual work usually pretty straight forward.

I think I'd start by lurking in these here newsgroups and even buy a book.

Debra Dalgleish has a big list of books for excel at:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. I think that John Green
(and others) is nice, too (for a second book??). See if you can find them
in your local bookstore and you can choose what one you like best.

xcelion wrote:

Hi Dave,

Thanks a lot for rewriiting the code itself.Iam new to Vba coding
That's why code look some what less readable.Any way i admire your
coding .Checking for the object every time is the one thing i have to
look after.Please advise me on the good coding tips to prevent program
crashes .I will try your fuction and get back to you.Thanks a lot

Thanks
Xcelion :)

--
xcelion
------------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=313732

--

Dave Peterson