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

[S Davis]

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

 

[S Davis]

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.

 

[JLGWhiz]

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]

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