Help! Is it Possibe to write UDF(custom formula) inside sheet

[xcelion]

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

 

[Tom Ogilvy]

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

 

[xcelion]

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(rngResult,rngSalary.Value, 3)
End If

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

Thanks BoB

Thanks for your help,I have used your formula

Sorry for replying lat

 

[Bob Phillips]

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

Regards

Bob