Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Iterating through a collection (For Each)
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 26
Default Iterating through a collection (For Each)
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Iterating through a collection (For Each)
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
'<<=============


---
Regards,
Norman


"Memento" wrote in message
...
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 26
Default Iterating through a collection (For Each)
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" wrote:

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
'<<=============


---
Regards,
Norman


"Memento" wrote in message
...
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Iterating through a collection (For Each)
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


---
Regards,
Norman


"Memento" wrote in message
...
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" wrote:

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
'<<=============


---
Regards,
Norman


"Memento" wrote in message
...
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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 26
Default Iterating through a collection (For Each)
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 he

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


"Norman Jones" wrote:

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


---
Regards,
Norman


"Memento" wrote in message
...
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" wrote:

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
'<<=============


---
Regards,
Norman


"Memento" wrote in message
...
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






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Iterating through a collection (For Each)
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 he

"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


---
Regards,
Norman


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 26
Default Iterating through a collection (For Each)
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

"Memento" wrote:

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 he

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


"Norman Jones" wrote:

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


---
Regards,
Norman


"Memento" wrote in message
...
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" wrote:

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
'<<=============


---
Regards,
Norman


"Memento" wrote in message
...
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






Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Iterating through a collection (For Each)
Hi Memento,

I

--
---
Regards,
Norman
Microsoft Excel MVP
"Memento" wrote in message
...
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

"Memento" wrote:

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 he

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


"Norman Jones" wrote:

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


---
Regards,
Norman


"Memento" wrote in message
...
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" wrote:

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
'<<=============


---
Regards,
Norman


"Memento" wrote in message
...
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








Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Iterating through a collection (For Each)
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
'<<=============


---
Regards,
Norman


"Memento" wrote in message
...
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

"Memento" wrote:


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
iterating groups? Mike[_98_] Excel Programming 1 September 21st 05 11:11 AM
iterating checkboxes [email protected] Excel Discussion (Misc queries) 1 May 11th 05 08:42 PM
Iterating Thru Cells Kelvin Clayson Excel Programming 6 November 9th 04 06:33 PM
Iterating through cells problem heenchi Excel Programming 1 June 9th 04 06:25 PM
iterating through all properties of an object Barney Fife Excel Programming 4 August 22nd 03 03:18 AM


All times are GMT +1. The time now is 10:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement