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!
"NickHK" wrote:
> 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
>
> "Memento" <(E-Mail Removed)> wrote in message
> news:7BFE4525-86BB-4542-8DF0-(E-Mail Removed)...
> > 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
> >
>
>
>
|
Similar Threads
