Help me with this UDF? Complex nested IFs...

Help me with this UDF? Complex nested IFs...

Post by S Davi » Sat, 18 Nov 2006 06:47:01


i 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
 
 
 

Help me with this UDF? Complex nested IFs...

Post by S Davi » Sat, 18 Nov 2006 06:47:26

i 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

 
 
 

Help me with this UDF? Complex nested IFs...

Post by S Davi » Sat, 18 Nov 2006 07:24:57

roblem 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:

 
 
 

Help me with this UDF? Complex nested IFs...

Post by SkxHV2hpe » Sat, 18 Nov 2006 07:40:01

ust 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:

 
 
 

Help me with this UDF? Complex nested IFs...

Post by S Davi » Sun, 19 Nov 2006 06:39:26

hanks! That actually sped up the calculation time by 30 seconds, and
made it a lot more readable.

JLGWhiz wrote: