For Each and Collections

[Guest]

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

 

[Guest]

Hi

I havent used collections for a long while (I am now a scripting.dictionary
fan) - But surely your issue is that you are comparing your value to the
iterator - i.e. I would have expected to see:

if hCollection(hCItem) = sDate Then

 

[Guest]

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?

 

[Bob Phillips]

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 String
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 3
hCollection.Add Item:=hColRange.Cells(hCItemCounter).Value, _
Key:=Format(hKeyRange.Cells(hCItemCounter).Value, "yyyymmdd")
Next hCItemCounter
On Error Resume Next
hCItem = hCollection(Format(sDate, "yyyymmdd"))
On Error GoTo 0
If hCItem <> "" Then
Toef = (uitVM - inVM) + (uitNM - inNM) + NaR + CAD
Else
Toef = ""
End If

End Function

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

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

 

[Guest]

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...

 

[Dana DeLouis]

Any ideas here?

Just guessing of course.

"25 dec", "1 jan", "9 apr"

These appear to be strings, but I'm not sure what date you are passing to
the function.

Function Toef(sDate As Date...

Comparing Dates is always a little red flag to watch out for.
For debugging, I might separate the key first to see what's being added...

MyKey =CStr(hKeyRange.Cells(hCItemCounter).Value)
MyItem = hColRange.Cells(hCItemCounter).Value

hCollection.Add MyItem, MyKey

Function Toef(sDate As Date, bNPrestaties As Integer, vCode As String,

I don't see "bNPrestaties" and "vCode " being used in the code. Are these
required?

It appears you are looping on the collection just to see if a key exists.
Perhaps just test for the key and see if there's an error.

Sub Demo()
Dim x
Dim Col As Collection

Set Col = New Collection

Col.Add 1, "a"
Col.Add 2, "b"
Col.Add 3, "c"

On Error Resume Next
x = Col("z")
If Err.Number > 0 Then
'Doesn't exists
Err.Clear
Else
'Toef = (uitVM - inVM) + (uitNM - inNM) + NaR + CAD
End If
End Sub

I'm a big fan of the Dictionary object also, as it can test for an item
without raising an error.