function recognizing that there is no inout in a cell

[whelanj]

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

 

[mangesh_yadav]

just use

if value = ""

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

- Manges

 

[Frank Kabel]

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)

 

[keepITcool]

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 abel]

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

 

[whelanj]

thanks to everyone