|
Help me with this UDF? Complex nested IFs...
Hi guys,
Thanks for looking at this. I have a custom function written that has performed flawlessly up until about a minute ago when I added one more line. Here is the non-working version I am dealing with right now: (edit - cliffnotes: I need RegCell1 to return itself as text - instead, it returns as a date/time, when all other regcells, if returned, come out as text. Makes it impossible to use in Access. Please read on for more details) **************** Function SDRank(PreviousBus, CurrentBus, InspectionNumber, CVIPCell, RegCell1, RegCell2, RegCell3, RegCell4, RegCell5, RegCell6, RegCell7, RegCell8, RegCell9, RegCell10, RegCell11, RegCell12, RegCellneg1, RegCellneg2, RegCellneg3, RegCellneg4, RegCellneg5, RegCellneg6, RegCellneg7, RegCellneg8, RegCellneg9, RegCellneg10, RegCellneg11) As String If InspectionNumber = 1 Then If PreviousBus < CurrentBus Then If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) = 32 Then SDRank = "OverDue" Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else If CVIPCell RegCell1 And CVIPCell < RegCell2 Then SDRank = RegCell2 Else If CVIPCell RegCell2 And CVIPCell < RegCell3 Then SDRank = RegCell3 Else If CVIPCell RegCell3 And CVIPCell < RegCell4 Then SDRank = RegCell4 Else If CVIPCell RegCell4 And CVIPCell < RegCell5 Then SDRank = RegCell5 Else If CVIPCell RegCell5 And CVIPCell < RegCell6 Then SDRank = RegCell6 Else If CVIPCell RegCell6 And CVIPCell < RegCell7 Then SDRank = RegCell7 Else If CVIPCell RegCell7 And CVIPCell < RegCell8 Then SDRank = RegCell8 Else If CVIPCell RegCell8 And CVIPCell < RegCell9 Then SDRank = RegCell9 Else If CVIPCell RegCell9 And CVIPCell < RegCell10 Then SDRank = RegCell10 Else If CVIPCell RegCell10 And CVIPCell < RegCell11 Then SDRank = RegCell11 Else If CVIPCell RegCell11 And CVIPCell < RegCell12 Then SDRank = RegCell12 Else SDRank = "" End If End If End If End If End If End If End If End If End If End If End If End If End If Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) = 32 Then SDRank = "OverDue" Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else If CVIPCell RegCell1 And CVIPCell < RegCell2 Then SDRank = RegCell2 Else If CVIPCell RegCell2 And CVIPCell < RegCell3 Then SDRank = RegCell3 Else If CVIPCell RegCell3 And CVIPCell < RegCell4 Then SDRank = RegCell4 Else If CVIPCell RegCell4 And CVIPCell < RegCell5 Then SDRank = RegCell5 Else If CVIPCell RegCell5 And CVIPCell < RegCell6 Then SDRank = RegCell6 Else If CVIPCell RegCell6 And CVIPCell < RegCell7 Then SDRank = RegCell7 Else If CVIPCell RegCell7 And CVIPCell < RegCell8 Then SDRank = RegCell8 Else If CVIPCell RegCell8 And CVIPCell < RegCell9 Then SDRank = RegCell9 Else If CVIPCell RegCell9 And CVIPCell < RegCell10 Then SDRank = RegCell10 Else If CVIPCell RegCell10 And CVIPCell < RegCell11 Then SDRank = RegCell11 Else If CVIPCell RegCell11 And CVIPCell < RegCell12 Then SDRank = RegCell12 Else SDRank = "" End If End If End If End If End If End If End If End If End If End If End If End If End If End If ************** Obviously, this is unfinished... it goes on for 12 inspection numbers and is essentially used to shift a range around up or down 12 cells in order to rank and return the closest equivalent value based on that ranking in the dynamic range, with InspectionNumber determining the range. This formula returns everything that I want it to. The problem is the formatting of the returned results. RegCellneg11 through RegCell12 are the shifting range and refer to the same cells of the same datatype. When this condition is met: " If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else..." (meaning that it returns the value of RegCell1) the format comes back as date/time. However, whenever any other condition is met, the result comes back as text! Now it is only possible for this to occur when InspectionNumber = 1, and so I am only ever getting one instance of date/time out of 12 times this occurs (InspectionNumbers 1 - 12)... I only say this because I am dumping this data back into Access for manipulation, Excel messing up the data formats means that Access can't recognize the data. If you've read this far and have any ideas on how to force RegCell1 to return itself as text, please reply here! Thanks!:) -Sean |
Help me with this UDF? Complex nested IFs...
Problem solved.
I am shamed:( Had a function stuck on the front of it to pull in data on certain conditions... basic stuff, just missed it, sorry. S Davis wrote: Hi guys, Thanks for looking at this. I have a custom function written that has performed flawlessly up until about a minute ago when I added one more line. Here is the non-working version I am dealing with right now: (edit - cliffnotes: I need RegCell1 to return itself as text - instead, it returns as a date/time, when all other regcells, if returned, come out as text. Makes it impossible to use in Access. Please read on for more details) **************** Function SDRank(PreviousBus, CurrentBus, InspectionNumber, CVIPCell, RegCell1, RegCell2, RegCell3, RegCell4, RegCell5, RegCell6, RegCell7, RegCell8, RegCell9, RegCell10, RegCell11, RegCell12, RegCellneg1, RegCellneg2, RegCellneg3, RegCellneg4, RegCellneg5, RegCellneg6, RegCellneg7, RegCellneg8, RegCellneg9, RegCellneg10, RegCellneg11) As String If InspectionNumber = 1 Then If PreviousBus < CurrentBus Then If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) = 32 Then SDRank = "OverDue" Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else If CVIPCell RegCell1 And CVIPCell < RegCell2 Then SDRank = RegCell2 Else If CVIPCell RegCell2 And CVIPCell < RegCell3 Then SDRank = RegCell3 Else If CVIPCell RegCell3 And CVIPCell < RegCell4 Then SDRank = RegCell4 Else If CVIPCell RegCell4 And CVIPCell < RegCell5 Then SDRank = RegCell5 Else If CVIPCell RegCell5 And CVIPCell < RegCell6 Then SDRank = RegCell6 Else If CVIPCell RegCell6 And CVIPCell < RegCell7 Then SDRank = RegCell7 Else If CVIPCell RegCell7 And CVIPCell < RegCell8 Then SDRank = RegCell8 Else If CVIPCell RegCell8 And CVIPCell < RegCell9 Then SDRank = RegCell9 Else If CVIPCell RegCell9 And CVIPCell < RegCell10 Then SDRank = RegCell10 Else If CVIPCell RegCell10 And CVIPCell < RegCell11 Then SDRank = RegCell11 Else If CVIPCell RegCell11 And CVIPCell < RegCell12 Then SDRank = RegCell12 Else SDRank = "" End If End If End If End If End If End If End If End If End If End If End If End If End If Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) = 32 Then SDRank = "OverDue" Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else If CVIPCell RegCell1 And CVIPCell < RegCell2 Then SDRank = RegCell2 Else If CVIPCell RegCell2 And CVIPCell < RegCell3 Then SDRank = RegCell3 Else If CVIPCell RegCell3 And CVIPCell < RegCell4 Then SDRank = RegCell4 Else If CVIPCell RegCell4 And CVIPCell < RegCell5 Then SDRank = RegCell5 Else If CVIPCell RegCell5 And CVIPCell < RegCell6 Then SDRank = RegCell6 Else If CVIPCell RegCell6 And CVIPCell < RegCell7 Then SDRank = RegCell7 Else If CVIPCell RegCell7 And CVIPCell < RegCell8 Then SDRank = RegCell8 Else If CVIPCell RegCell8 And CVIPCell < RegCell9 Then SDRank = RegCell9 Else If CVIPCell RegCell9 And CVIPCell < RegCell10 Then SDRank = RegCell10 Else If CVIPCell RegCell10 And CVIPCell < RegCell11 Then SDRank = RegCell11 Else If CVIPCell RegCell11 And CVIPCell < RegCell12 Then SDRank = RegCell12 Else SDRank = "" End If End If End If End If End If End If End If End If End If End If End If End If End If End If ************** Obviously, this is unfinished... it goes on for 12 inspection numbers and is essentially used to shift a range around up or down 12 cells in order to rank and return the closest equivalent value based on that ranking in the dynamic range, with InspectionNumber determining the range. This formula returns everything that I want it to. The problem is the formatting of the returned results. RegCellneg11 through RegCell12 are the shifting range and refer to the same cells of the same datatype. When this condition is met: " If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else..." (meaning that it returns the value of RegCell1) the format comes back as date/time. However, whenever any other condition is met, the result comes back as text! Now it is only possible for this to occur when InspectionNumber = 1, and so I am only ever getting one instance of date/time out of 12 times this occurs (InspectionNumbers 1 - 12)... I only say this because I am dumping this data back into Access for manipulation, Excel messing up the data formats means that Access can't recognize the data. If you've read this far and have any ideas on how to force RegCell1 to return itself as text, please reply here! Thanks!:) -Sean |
Help me with this UDF? Complex nested IFs...
Just a comment: The following is an extract from VBA Help.
Function Bonus(performance, salary) If performance = 1 Then Bonus = salary * 0.1 ElseIf performance = 2 Then Bonus = salary * 0.09 ElseIf performance = 3 Then Bonus = salary * 0.07 Else Bonus = 0 End If End Function By using the ElseIf statement, only one End If is needed to close the block. I noticed that you had quite an array there. "S Davis" wrote: Problem solved. I am shamed:( Had a function stuck on the front of it to pull in data on certain conditions... basic stuff, just missed it, sorry. S Davis wrote: Hi guys, Thanks for looking at this. I have a custom function written that has performed flawlessly up until about a minute ago when I added one more line. Here is the non-working version I am dealing with right now: (edit - cliffnotes: I need RegCell1 to return itself as text - instead, it returns as a date/time, when all other regcells, if returned, come out as text. Makes it impossible to use in Access. Please read on for more details) **************** Function SDRank(PreviousBus, CurrentBus, InspectionNumber, CVIPCell, RegCell1, RegCell2, RegCell3, RegCell4, RegCell5, RegCell6, RegCell7, RegCell8, RegCell9, RegCell10, RegCell11, RegCell12, RegCellneg1, RegCellneg2, RegCellneg3, RegCellneg4, RegCellneg5, RegCellneg6, RegCellneg7, RegCellneg8, RegCellneg9, RegCellneg10, RegCellneg11) As String If InspectionNumber = 1 Then If PreviousBus < CurrentBus Then If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) = 32 Then SDRank = "OverDue" Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else If CVIPCell RegCell1 And CVIPCell < RegCell2 Then SDRank = RegCell2 Else If CVIPCell RegCell2 And CVIPCell < RegCell3 Then SDRank = RegCell3 Else If CVIPCell RegCell3 And CVIPCell < RegCell4 Then SDRank = RegCell4 Else If CVIPCell RegCell4 And CVIPCell < RegCell5 Then SDRank = RegCell5 Else If CVIPCell RegCell5 And CVIPCell < RegCell6 Then SDRank = RegCell6 Else If CVIPCell RegCell6 And CVIPCell < RegCell7 Then SDRank = RegCell7 Else If CVIPCell RegCell7 And CVIPCell < RegCell8 Then SDRank = RegCell8 Else If CVIPCell RegCell8 And CVIPCell < RegCell9 Then SDRank = RegCell9 Else If CVIPCell RegCell9 And CVIPCell < RegCell10 Then SDRank = RegCell10 Else If CVIPCell RegCell10 And CVIPCell < RegCell11 Then SDRank = RegCell11 Else If CVIPCell RegCell11 And CVIPCell < RegCell12 Then SDRank = RegCell12 Else SDRank = "" End If End If End If End If End If End If End If End If End If End If End If End If End If Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) = 32 Then SDRank = "OverDue" Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else If CVIPCell RegCell1 And CVIPCell < RegCell2 Then SDRank = RegCell2 Else If CVIPCell RegCell2 And CVIPCell < RegCell3 Then SDRank = RegCell3 Else If CVIPCell RegCell3 And CVIPCell < RegCell4 Then SDRank = RegCell4 Else If CVIPCell RegCell4 And CVIPCell < RegCell5 Then SDRank = RegCell5 Else If CVIPCell RegCell5 And CVIPCell < RegCell6 Then SDRank = RegCell6 Else If CVIPCell RegCell6 And CVIPCell < RegCell7 Then SDRank = RegCell7 Else If CVIPCell RegCell7 And CVIPCell < RegCell8 Then SDRank = RegCell8 Else If CVIPCell RegCell8 And CVIPCell < RegCell9 Then SDRank = RegCell9 Else If CVIPCell RegCell9 And CVIPCell < RegCell10 Then SDRank = RegCell10 Else If CVIPCell RegCell10 And CVIPCell < RegCell11 Then SDRank = RegCell11 Else If CVIPCell RegCell11 And CVIPCell < RegCell12 Then SDRank = RegCell12 Else SDRank = "" End If End If End If End If End If End If End If End If End If End If End If End If End If End If ************** Obviously, this is unfinished... it goes on for 12 inspection numbers and is essentially used to shift a range around up or down 12 cells in order to rank and return the closest equivalent value based on that ranking in the dynamic range, with InspectionNumber determining the range. This formula returns everything that I want it to. The problem is the formatting of the returned results. RegCellneg11 through RegCell12 are the shifting range and refer to the same cells of the same datatype. When this condition is met: " If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else..." (meaning that it returns the value of RegCell1) the format comes back as date/time. However, whenever any other condition is met, the result comes back as text! Now it is only possible for this to occur when InspectionNumber = 1, and so I am only ever getting one instance of date/time out of 12 times this occurs (InspectionNumbers 1 - 12)... I only say this because I am dumping this data back into Access for manipulation, Excel messing up the data formats means that Access can't recognize the data. If you've read this far and have any ideas on how to force RegCell1 to return itself as text, please reply here! Thanks!:) -Sean |
Help me with this UDF? Complex nested IFs...
Thanks! That actually sped up the calculation time by 30 seconds, and
made it a lot more readable. JLGWhiz wrote: Just a comment: The following is an extract from VBA Help. Function Bonus(performance, salary) If performance = 1 Then Bonus = salary * 0.1 ElseIf performance = 2 Then Bonus = salary * 0.09 ElseIf performance = 3 Then Bonus = salary * 0.07 Else Bonus = 0 End If End Function By using the ElseIf statement, only one End If is needed to close the block. I noticed that you had quite an array there. "S Davis" wrote: Problem solved. I am shamed:( Had a function stuck on the front of it to pull in data on certain conditions... basic stuff, just missed it, sorry. S Davis wrote: Hi guys, Thanks for looking at this. I have a custom function written that has performed flawlessly up until about a minute ago when I added one more line. Here is the non-working version I am dealing with right now: (edit - cliffnotes: I need RegCell1 to return itself as text - instead, it returns as a date/time, when all other regcells, if returned, come out as text. Makes it impossible to use in Access. Please read on for more details) **************** Function SDRank(PreviousBus, CurrentBus, InspectionNumber, CVIPCell, RegCell1, RegCell2, RegCell3, RegCell4, RegCell5, RegCell6, RegCell7, RegCell8, RegCell9, RegCell10, RegCell11, RegCell12, RegCellneg1, RegCellneg2, RegCellneg3, RegCellneg4, RegCellneg5, RegCellneg6, RegCellneg7, RegCellneg8, RegCellneg9, RegCellneg10, RegCellneg11) As String If InspectionNumber = 1 Then If PreviousBus < CurrentBus Then If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) = 32 Then SDRank = "OverDue" Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else If CVIPCell RegCell1 And CVIPCell < RegCell2 Then SDRank = RegCell2 Else If CVIPCell RegCell2 And CVIPCell < RegCell3 Then SDRank = RegCell3 Else If CVIPCell RegCell3 And CVIPCell < RegCell4 Then SDRank = RegCell4 Else If CVIPCell RegCell4 And CVIPCell < RegCell5 Then SDRank = RegCell5 Else If CVIPCell RegCell5 And CVIPCell < RegCell6 Then SDRank = RegCell6 Else If CVIPCell RegCell6 And CVIPCell < RegCell7 Then SDRank = RegCell7 Else If CVIPCell RegCell7 And CVIPCell < RegCell8 Then SDRank = RegCell8 Else If CVIPCell RegCell8 And CVIPCell < RegCell9 Then SDRank = RegCell9 Else If CVIPCell RegCell9 And CVIPCell < RegCell10 Then SDRank = RegCell10 Else If CVIPCell RegCell10 And CVIPCell < RegCell11 Then SDRank = RegCell11 Else If CVIPCell RegCell11 And CVIPCell < RegCell12 Then SDRank = RegCell12 Else SDRank = "" End If End If End If End If End If End If End If End If End If End If End If End If End If Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) = 32 Then SDRank = "OverDue" Else If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else If CVIPCell RegCell1 And CVIPCell < RegCell2 Then SDRank = RegCell2 Else If CVIPCell RegCell2 And CVIPCell < RegCell3 Then SDRank = RegCell3 Else If CVIPCell RegCell3 And CVIPCell < RegCell4 Then SDRank = RegCell4 Else If CVIPCell RegCell4 And CVIPCell < RegCell5 Then SDRank = RegCell5 Else If CVIPCell RegCell5 And CVIPCell < RegCell6 Then SDRank = RegCell6 Else If CVIPCell RegCell6 And CVIPCell < RegCell7 Then SDRank = RegCell7 Else If CVIPCell RegCell7 And CVIPCell < RegCell8 Then SDRank = RegCell8 Else If CVIPCell RegCell8 And CVIPCell < RegCell9 Then SDRank = RegCell9 Else If CVIPCell RegCell9 And CVIPCell < RegCell10 Then SDRank = RegCell10 Else If CVIPCell RegCell10 And CVIPCell < RegCell11 Then SDRank = RegCell11 Else If CVIPCell RegCell11 And CVIPCell < RegCell12 Then SDRank = RegCell12 Else SDRank = "" End If End If End If End If End If End If End If End If End If End If End If End If End If End If ************** Obviously, this is unfinished... it goes on for 12 inspection numbers and is essentially used to shift a range around up or down 12 cells in order to rank and return the closest equivalent value based on that ranking in the dynamic range, with InspectionNumber determining the range. This formula returns everything that I want it to. The problem is the formatting of the returned results. RegCellneg11 through RegCell12 are the shifting range and refer to the same cells of the same datatype. When this condition is met: " If CVIPCell < RegCell1 And (RegCell1 - CVIPCell) <= 31 Then SDRank = RegCell1 Else..." (meaning that it returns the value of RegCell1) the format comes back as date/time. However, whenever any other condition is met, the result comes back as text! Now it is only possible for this to occur when InspectionNumber = 1, and so I am only ever getting one instance of date/time out of 12 times this occurs (InspectionNumbers 1 - 12)... I only say this because I am dumping this data back into Access for manipulation, Excel messing up the data formats means that Access can't recognize the data. If you've read this far and have any ideas on how to force RegCell1 to return itself as text, please reply here! Thanks!:) -Sean |
| All times are GMT +1. The time now is 06:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com