New to functions, 'If' isn't working for me

[Guest]

Hi,
I am trying to write my first function. Tha aim is to add an extra column
and populate it with categories for each record. Hopefully the code below
will help in understanding:

Function undispatched_age_bucket(ByVal business, ByVal Paper_vs_Elec, ByVal
dispatch_age, ByVal credit_paper_benchmark, _
ByVal rates_paper_benchmark, ByVal gme_paper_benchmark, ByVal
other_paper_benchmark) As Variant

If IsNull(business) Or IsNull(Paper_vs_Elec) Or IsNull(dispatch_age)
Then credit_paper_benchmark = ""

If PapervsElec = "p" Then

If business = "Credit" And dispatch_age > credit_paper_benchmark Then
undispatched_age_bucket = ">" & credit_paper_benchmark
Else
undispatched_age_bucket = "<=" & credit_paper_benchmark
End If

If business = "Rates" And dispatch_age > rates_paper_benchmark Then
undispatched_age_bucket = ">" & rates_paper_benchmark
Else
undispatched_age_bucket = "<=" & rates_paper_benchmark
End If

If business = "GME" And dispatch_age > gme_paper_benchmark Then
undispatched_age_bucket = ">" & gme_paper_benchmark
Else
undispatched_age_bucket = "<=" & gme_paper_benchmark
End If

If business = "Other" And dispatch_age > other_paper_benchmark Then
undispatched_age_bucket = ">" & other_paper_benchmark
Else
undispatched_age_bucket = "<=" & other_paper_benchmark
End If

Else
IsNull (undispatched_age)
End If

End Function


I select the values for 'paper vs elec', 'business' and 'dispatch age' and
type in the values for the benchmarks. When I try to drag the field down it
simply repeats the first value it generates over and over and doesn't seem to
caculate a value.
Can anyone help??
Many thanks in advance.
-Ben

 

[Niek Otten]

Hi Ben,

You can not change anything in a worksheet from a function; you need a Sub to do that.
Functions can only return a value to replace the function call.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
| I am trying to write my first function. Tha aim is to add an extra column
| and populate it with categories for each record. Hopefully the code below
| will help in understanding:
|
| Function undispatched_age_bucket(ByVal business, ByVal Paper_vs_Elec, ByVal
| dispatch_age, ByVal credit_paper_benchmark, _
| ByVal rates_paper_benchmark, ByVal gme_paper_benchmark, ByVal
| other_paper_benchmark) As Variant
|
| If IsNull(business) Or IsNull(Paper_vs_Elec) Or IsNull(dispatch_age)
| Then credit_paper_benchmark = ""
|
| If PapervsElec = "p" Then
|
| If business = "Credit" And dispatch_age > credit_paper_benchmark Then
| undispatched_age_bucket = ">" & credit_paper_benchmark
| Else
| undispatched_age_bucket = "<=" & credit_paper_benchmark
| End If
|
| If business = "Rates" And dispatch_age > rates_paper_benchmark Then
| undispatched_age_bucket = ">" & rates_paper_benchmark
| Else
| undispatched_age_bucket = "<=" & rates_paper_benchmark
| End If
|
| If business = "GME" And dispatch_age > gme_paper_benchmark Then
| undispatched_age_bucket = ">" & gme_paper_benchmark
| Else
| undispatched_age_bucket = "<=" & gme_paper_benchmark
| End If
|
| If business = "Other" And dispatch_age > other_paper_benchmark Then
| undispatched_age_bucket = ">" & other_paper_benchmark
| Else
| undispatched_age_bucket = "<=" & other_paper_benchmark
| End If
|
| Else
| IsNull (undispatched_age)
| End If
|
| End Function
|
|
| I select the values for 'paper vs elec', 'business' and 'dispatch age' and
| type in the values for the benchmarks. When I try to drag the field down it
| simply repeats the first value it generates over and over and doesn't seem to
| caculate a value.
| Can anyone help??
| Many thanks in advance.
| -Ben