Comparing a range of values against a cell

[Guest]

Hello Guys,

This code kills me at the moment. I've already realized that working with
ranges etc, can be quite cumbersome and tricky:

Function VBCode(vCode As String, Jobtime As Single, Roster As Single) As
Single
Dim cell As Variant
Set cRange = Worksheets("CODE").range("CodeNorm")
For Each cell In cRange
If cell = vCode Then
VBCode = Jobtime / 5
Else
VBCode = ""
End If
Next cell
End Function

The range "CodeNorm" are just short strings.

The crazy thing is that when i use this function in a cell, it keeps giving
me a "a value in the cell has a incorrect datatype". When i put a breakpoint
at "If cell = vCode then", I can see they all contain the actual string
values I need for this to work. So i'm kinda lost here, I don't know why this
doesn't work as it needs to be...

Anyone can easily recreate this by creating some string items like "ADV,
TOP, BUJ", etc... in a few adjacent cells, make it a named range "CodeNorm",
create the above function in a module, and try the function in a cell.

Anyone knows why this doesn't work?

Thanks in advance guys.

Memento

 

[NickHK]

It always helps to avoid Variants unless you really need one.
Also, there's no point looping all cells in that range, as only the last
comparison will matter and its result returned.
So either use .End(xlDown) or exit the loop once some criteria is reached.
But I suspect your logic is not correct in applying this code.
Also, Roster serves no purpose.
But anyway...

Function VBCode(vCode As String, Jobtime As Single, Roster As Single) As
Single
Dim cell As Range

For Each cell In Worksheets("CODE").range("CodeNorm")
If cell.Value = vCode Then
VBCode = Jobtime / 5
Else
'VBCode = ""
VBCode = 0 'As the function is supposed to return a single
End If
Next cell

End Function

NickHK

 

[Guest]

Yes NickHK I Know,

But i excluded that logic to make my point clearer, so i left out the Roster
(an ElseIf), and I use a Exit For, so the loop stops automatically when a
match is found.

Thanks a lot!