hey there. not much experience with VB. trying to make a book that th
user enters data in one spreadsheet and then based in a defined rang
in which the value falls, the UDF will be given an integer ranking i
another spreadsheet. a ranking system based on physical data.

the problem i'm having is that 0 is a significant value and having a
empty cell returns a integer rank which i DON'T want. is there any cod
that the UDF will recognize the cell is empty rather than a use
entered 0?

i was thinking of using strings but i have no idea how to do this i
VB. if this isn't clear let me know...

here's the code

*

Function UtilizationRank(Utilization As Integer) As Integer

If Utilization 90 Then
UtilizationRank = 1
ElseIf Utilization <= 90 And Utilization 75 Then
UtilizationRank = 2
ElseIf Utilization <= 75 And Utilization 50 Then
UtilizationRank = 3
ElseIf Utilization <= 50 And Utilization 0 Then
UtilizationRank = 4
ElseIf Utilization = 0 Then
UtilizationRank = 5
End If

End Function

*

... so an empty cell gives a rank of five which I don't want. help i
greatly apprechiated

--
Message posted from http://www.ExcelForum.com

just use

if value = ""

i.e. two double quotes which represents a blank string

- Manges

--
Message posted from http://www.ExcelForum.com

Hi
try the following UDF:
Function UtilizationRank(Util_rng As Range) As Integer
If Util_rng.Cells.Count 1 Then
UtilizationRank = CVErr(xlErrValue)
Exit Function
End If
If Util_rng.Value = "" Then
UtilizationRank = CVErr(xlErrValue)
Exit Function
End If
Select Case Util_rng.Value
Case Is 90
UtilizationRank = 1
Case Is 75
UtilizationRank = 2
Case Is 50
UtilizationRank = 3
Case Is 0
UtilizationRank = 4
Case 0
UtilizationRank = 5
Case Else
UtilizationRank = CVErr(xlErrValue)
End Select
End Function

Call it in your spreadhseet with
=UtilizationRank(A1)

-----Original Message-----
hey there. not much experience with VB. trying to make a
book that the
user enters data in one spreadsheet and then based in a
defined range
in which the value falls, the UDF will be given an
integer ranking in
another spreadsheet. a ranking system based on physical
data.

the problem i'm having is that 0 is a significant value
and having an
empty cell returns a integer rank which i DON'T want. is
there any code
that the UDF will recognize the cell is empty rather than
a user
entered 0?

i was thinking of using strings but i have no idea how to
do this in
VB. if this isn't clear let me know...

here's the code

*

Function UtilizationRank(Utilization As Integer) As
Integer

If Utilization 90 Then
UtilizationRank = 1
ElseIf Utilization <= 90 And Utilization 75 Then
UtilizationRank = 2
ElseIf Utilization <= 75 And Utilization 50 Then
UtilizationRank = 3
ElseIf Utilization <= 50 And Utilization 0 Then
UtilizationRank = 4
ElseIf Utilization = 0 Then
UtilizationRank = 5
End If

End Function

*

... so an empty cell gives a rank of five which I don't
want. help is
greatly apprechiated.


---
Message posted from http://www.ExcelForum.com/

.

Frank,

for the CVERR to return anything else then a standard #value error,
(xlErrNA,xlErrNull) the function should return a Variant

also it test for empty cells only, while text cells will count as 0-5

it will work for what the OP had in mind.. I just noticed <g


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Frank Kabel" wrote:

Hi
try the following UDF:
Function UtilizationRank(Util_rng As Range) As Integer
If Util_rng.Cells.Count 1 Then
UtilizationRank = CVErr(xlErrValue)
Exit Function
End If
If Util_rng.Value = "" Then
UtilizationRank = CVErr(xlErrValue)
Exit Function
End If
Select Case Util_rng.Value
Case Is 90
UtilizationRank = 1
Case Is 75
UtilizationRank = 2
Case Is 50
UtilizationRank = 3
Case Is 0
UtilizationRank = 4
Case 0
UtilizationRank = 5
Case Else
UtilizationRank = CVErr(xlErrValue)
End Select
End Function

Call it in your spreadhseet with
=UtilizationRank(A1)

Hi
thanks for your comments. Though the function works also
with Integer as return defined. But below a function which
will also cover text values :-)


Function UtilizationRank(Util_rng As Range) As Variant
If Util_rng.Cells.Count 1 Then
UtilizationRank = CVErr(xlErrValue)
Exit Function
End If
If Util_rng.Value = "" Then
UtilizationRank = CVErr(xlErrValue)
Exit Function
End If
If Not IsNumeric(Util_rng.Value) Then
UtilizationRank = CVErr(xlErrValue)
Exit Function
End If
Select Case Util_rng.Value
Case Is 90
UtilizationRank = 1
Case Is 75
UtilizationRank = 2
Case Is 50
UtilizationRank = 3
Case Is 0
UtilizationRank = 4
Case 0
UtilizationRank = 5
Case Else
UtilizationRank = CVErr(xlErrValue)
End Select
End Function

-----Original Message-----
Frank,

for the CVERR to return anything else then a standard
#value error,
(xlErrNA,xlErrNull) the function should return a Variant

also it test for empty cells only, while text cells will
count as 0-5

it will work for what the OP had in mind.. I just noticed
<g


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Frank Kabel" wrote:

Hi
try the following UDF:
Function UtilizationRank(Util_rng As Range) As Integer
If Util_rng.Cells.Count 1 Then
UtilizationRank = CVErr(xlErrValue)
Exit Function
End If
If Util_rng.Value = "" Then
UtilizationRank = CVErr(xlErrValue)
Exit Function
End If
Select Case Util_rng.Value
Case Is 90
UtilizationRank = 1
Case Is 75
UtilizationRank = 2
Case Is 50
UtilizationRank = 3
Case Is 0
UtilizationRank = 4
Case 0
UtilizationRank = 5
Case Else
UtilizationRank = CVErr(xlErrValue)
End Select
End Function

Call it in your spreadhseet with
=UtilizationRank(A1)

.

thanks to everyone

--
Message posted from http://www.ExcelForum.com