Hi All,

Iam a excel newbie. I have a custom formula written as UDF .When iam
writting this fucntion inside a sheet iam not able to call it as
formula in the same sheet.It's giving ?NAME error But iam writting the
UDF inside a module it's working Can any one help in solving the
problem.Is it possible to write write custom formula inside a module
only ?
Any one please help

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=386393

Yes, the UDF should be in a general module -- not a sheet module or the
thisworkbook module.

--
Regards,
Tom Ogilvy

"xcelion" wrote in
message ...

Hi All,

Iam a excel newbie. I have a custom formula written as UDF .When iam
writting this fucntion inside a sheet iam not able to call it as
formula in the same sheet.It's giving ?NAME error But iam writting the
UDF inside a module it's working Can any one help in solving the
problem.Is it possible to write write custom formula inside a module
only ?
Any one please help

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=386393

Hi Tom,
Thanks Tom
Thanks a lot
Your advice has saved me lot of time .But created another problem.Now
i have to covert my UDF to excel formula to meet my requirement .My
requirement is like this

I have two input

1.Job title
2.salary

From the JobTile i have to find the Job category from lookup table and
from Job category i have to find the salary range from another look
table and then apply the PERCENTILERANK fuction on that range and if it
falls below range it should give Below,and above if it is greater .Iam
attaching the code for the UDF i have written(hope ever one understands
my logic :))) .Can any one convert it to formula .Is it possible to do
my requirement with formula itself or do i have to write UDF?





Code:
--------------------
Function GetRank(rngJobTitle As Range, rngSalary As Range)
'Custom function to find the rank

Dim wsCatLookup As Worksheet
Dim rngTemp, rngSalRng As Range, rngResult As Range, rngCatRange As Range
Dim LowerLimit As Long, UpperLimit As Long
Dim strCategory As String, strFormula As String, strJobTitle As String, strTemp As String
Dim bReturn As Boolean


Set wsCatLookup = ThisWorkbook.Sheets("SalaryRange")
Set rngTemp = wsCatLookup.Range("A4", wsCatLookup.Range("C4").End(xlDown))

Set rngSalRng = wsCatLookup.Range("E3", wsCatLookup.Range("G4").End(xlDown))


strJobTitle = CStr(rngJobTitle.Value)

If Len(strJobTitle) Then
strCategory = WorksheetFunction.VLOOKUP(strJobTitle, rngTemp, 2, False)
End If
Set rngResult = GetRangeFromCategory(strCategory)

If IsEmpty(rngSalary.Value) Then
GetRank = ""
ElseIf (rngSalary.Value < rngResult.Cells(1, 1).Value) Then
GetRank = "Below"
ElseIf (rngSalary.Value rngResult.Cells(1, 2).Value) Then
GetRank = "Above"
Else
GetRank = Application.WorksheetFunction.PercentRank(rngResul t,rngSalary.Value, 3)
End If

--------------------



Thanks in advance


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

the NAME error suggests it cannot find that UDF.

Did you save the UDF to a standard code module
?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"xcelion" wrote in
message ...

Hi All,

Iam a excel newbie. I have a custom formula written as UDF .When iam
writting this fucntion inside a sheet iam not able to call it as
formula in the same sheet.It's giving ?NAME error But iam writting the
UDF inside a module it's working Can any one help in solving the
problem.Is it possible to write write custom formula inside a module
only ?
Any one please help

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=386393

Hi Bob,

Tom Suggested that it's not possible to write UDF inside a sheet.So my
requirement now is is it possible to covert the UDF i have posted to
a normal excel formula


Thanks
xcelion


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

No, you can't write it in a sheet, but you can put it in a standard code
module.

But you should be able to do it with formulae

=VLOOKUP(VLOOKUP("dogsbody",jobs,2,FALSE),salaries ,2,FALSE)

Assuming that you have an nx2 table of job title and job category called
jobs, and an nx2 table of job categories and salaries called salaries, that
formula gives the category salary. You can compare the salary using
PERCENTILERANK.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"xcelion" wrote in
message ...

Hi Bob,

Tom Suggested that it's not possible to write UDF inside a sheet.So my
requirement now is is it possible to covert the UDF i have posted to
a normal excel formula


Thanks
xcelion


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

Thanks BoB

Thanks for your help,I have used your formula

Sorry for replying lat

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

No problems, at least you did let us know how you got on.

Regards

Bob


"xcelion" wrote in
message ...

Thanks BoB

Thanks for your help,I have used your formula

Sorry for replying late


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