So now break at where you are creating the collection and highlight each
expression, right click and "Add watch". THis will then show yopu whats
going on. GO back to basics, check your named ranges are correct etc...

"Memento" wrote:

Hmm, indeed. hCItem contains the value "Nieuwjaar", instead of "1 jan" at
which it should check against sDate.


"Ben McBen" wrote:

Mmmm... I think that perhaps the iterators for collections are the
values/objects, so this is not your issue here (I still recomend you check
out dictionaries). If you make a break at the line:

If hCItem = sDate Then


What values can you see in hCItem?

"Memento" wrote:

Hello Guys,

This one needs a new thread I think. I have a range(G5:G19) with following
values: "christmas", "newyear", "easter", etc.. another range (H5:H19) with
the values (or keys as i assume) "25 dec", "1 jan", "9 apr", etc... these key
values are a customized date datatype "d mmm", as you can see.

So in my code i defined two ranges, and i add the values and the keys to the
collection. Next I try to use a For Each... Next to be able to do something
for each item in the collection, and that's where it goes wrong.

The keys in the collection are the dates, but when i use my function, it
doesn't do anything on christmas, easter, or any other holiday... i get no
errors whatsoever anymore, so the code seem to be okay... So i'm kinda lost
here, no errors, no nothing... i've actually tried several other values (such
as dates), to change the datatype in the ranges, and than a #VALUE pops up in
the field where i use the "TOEF" function, so that seems to be working okay..

Any ideas here?

Function Toef(sDate As Date, bNPrestaties As Integer, vCode As String,
uitVM, _
inVM, uitNM, inNM, NaR, CAD As Single)
Dim hCollection As Collection
Dim hCItemCounter As Long
Dim hCItem As Variant
Dim hColRange As Range
Dim hKeyRange As Range
Set hColRange = Worksheets("CODE").Range("G5:G19")
Set hKeyRange = Worksheets("CODE").Range("H5:H19")
Set hCollection = New Collection
For hCItemCounter = 1 To 15
hCollection.Add Item:=hColRange.Cells(hCItemCounter).Value, _
Key:=CStr(hKeyRange.Cells(hCItemCounter).Value)
Next hCItemCounter
For Each hCItem In hCollection
If hCItem = sDate Then
Toef = (uitVM - inVM) + (uitNM - inNM) + NaR + CAD
Else
Toef = ""
End If
Next hCItem