Iterating through a collection (For Each)

[Guest]

Hello guys,

The following issue is at my hands:
Dim hCollection as New Collection
'declaration of the collection variables and assigning values to the
variables...

For Each hCollection.Item In hCollection
'If sDate = hCollection.Item(1) Or sDate = hCollection.Item(2) Or sDate =
'hCollection.Item(3) Or sDate = hCollection.Item(4) Or sDate =
hCollection.Item(5)
'Or sDate = hCollection.Item(6) Or sDate = hCollection.Item(7) Or sDate =
'hCollection.Item(8) Or sDate = hCollection.Item(9) 'Or sDate =
hCollection.Item(10) Then
Toef = (uitVM - inVM) + (uitNM - inNM) + NaR + CAD
Else
Toef = ""
End If

I am trying a different approach for the items that here are commented out
(and which works great). I'm trying a For Each here in the first line, but
the line "For Each hCollection.Item In hCollection" causes a "Argument not
Optional" error...

Thanks in advance guys

 

[Norman Jones]

Hi Memento,

Try something like:

'=============>>
Public Sub Tester()
Dim hCollection As Collection
Dim i As Long
Dim myItem As Variant

Set hCollection = New Collection

For i = 1 To 10
hCollection.Add Item:=Cells(i, 1).Value, _
Key:=CStr(Cells(i, 1).Value)
Next i

For Each myItem In hCollection
'Your code, e.g:
MsgBox myItem
Next myItem
End Sub
'<<=============

 

[Guest]

Okay Norman, i've been experimenting with your suggestion, and this is what
i've been able to do:

Dim hCollection As Collection
Dim hCItemCounter As Long
Dim hCItem As Variant
Dim hColRange As Range
Set hColRange = Worksheets("CODE").Range("G5:G25")
Dim hKeyRange As Range
Set hKeyRange = Worksheets("CODE").Range("H5:H25")
Set hCollection = New Collection
For hCItemCounter = 1 To 25
hCollection.Add Item:=hColRange.Value, Key:=hKeyRange.Value
Next hCItemCounter
For Each hCItem In hCollection
Toef = (uitVM - inVM) + (uitNM - inNM) + NaR + CAD
Next hCItem
Toef = ""

In your example you use the Cells property to get the items, considering
i've got my items and key items in ranges on another sheet, i've dimensioned
two new ranges, and set their respective contents to the items. However, when
I use this function, it gives me "Wrong datatype". The key items are actually
dates in the format "d mmm", but that shouldn't cause any problems. However I
am pretty sure it's the "Key:=hKeyRange.Value" that is causing the problem.
I'm also not converting to String as you did, because .Value does that
automatically (Variant)...

Any ideas?

 

[Norman Jones]

Hi Memento,

Try something like:

'=============>>
Public Sub Tester()
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:G25")
Set hKeyRange = Worksheets("CODE").Range("H5:H25")
Set hCollection = New Collection

For hCItemCounter = 1 To 25
hCollection.Add Item:= _
hColRange.Cells(hCItemCounter).Value, _
Key:=CStr(hKeyRange.Cells(hCItemCounter).Value)
Next hCItemCounter

'Your code

End Sub
'<<=============

BTW:

I'm also not converting to String as you did, because .Value does that
automatically (Variant)...

Consider the following results from the Immediate window:

Range("A1").Value =date
? Range("A1").Value
04/06/2007

?typename(Range("A1").Value)
Date

 

[Guest]

Okay Norman,

I've followed your instructions so far, it seems to work now, but it doesn't
seem to do the actual calculation...

This is what I got so far:

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:G25")
Set hKeyRange = Worksheets("CODE").Range("H5:H25")
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

It seems to go wrong here:

"For Each hCItem In hCollection
If hCItem = sDate Then
Toef = (uitVM - inVM) + (uitNM - inNM) + NaR + CAD"

 

[Norman Jones]

Hi Memento,

'----------------
I've followed your instructions so far, it seems to work now, but it doesn't
seem to do the actual calculation...

[...]

It seems to go wrong here:

"For Each hCItem In hCollection
If hCItem = sDate Then
Toef = (uitVM - inVM) + (uitNM - inNM) + NaR + CAD"
'----------------

I know nothing of any of the variables used in your
equality assignment.

I limited myself to responding to your substantive
question: the loading and iteration of the collection

 

[Guest]

I wasn't really clear in my last post...

Everything seems to work fine now, it doesn't give me a #VALUE error
anymore. But it doens't do the calculation in "Toef = (uitVM - inVM) + (uitNM
- inNM) + NaR + CAD", as if it doesn't see the value "1 jan" from sDate in
"hCItem"...

So no errors anymore, and it seems to work okay, but it doesn't do the
calculation on my holidays in the collection... The code:

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:G25")
Set hKeyRange = Worksheets("CODE").Range("H5:H25")
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

 

[Norman Jones]

Hi Memento,

I

 

[Norman Jones]

Hi Memento,

I may have misunderstood your data structure and I do
not know the purpose of your function.

However, the following returns *a* result - with my test
data:

'<<=============
Public Sub TestIt()
MsgBox Toef(#1/2/2007#, 2, "A", 9, 8, 7, 6, 5, 4)
End Sub

'------------------->
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("H5:H25") 'G5:G25"
Set hKeyRange = Worksheets("CODE").Range("G5:G25") 'H5:H25"
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
Exit Function
Else
Toef = ""
End If
Next hCItem
End Function
End Sub
'<<=============