Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New to functions, 'If' isn't working for me
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New to functions, 'If' isn't working for me
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 "Ben Antonio" wrote in message ... | 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel FUNCTIONS stop working | Excel Worksheet Functions | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Changing the range of several averaging functions | Excel Discussion (Misc queries) | |||
Are there functions that perform robust statistics in Excel? | Excel Worksheet Functions | |||
Application.Volatile not working as expected | Excel Discussion (Misc queries) |