Dear Group,

I am completely stumped by this. I am filling a dictionary and then
looping over an array made from its keys later. However, if I loop to
the count size of the dictionary I will hit an error. The reason seems
to be that the array that is made via the keys method, is one element
shorter than the dictionary count?

Sample code below:
Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'' INPUTS HERE ''
Public Const distance As Single = 26.1
Public lrods As Dictionary
Public lrods_1 As Dictionary
Public lrods_9 As Dictionary
Public worst As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''


Sub Identify_lrods()
Dim ws As Worksheet
Dim i As Integer
Set lrods = New Dictionary
Set lrods_1 = New Dictionary
Set lrods_9 = New Dictionary

'look at sheet 3
Set ws = ThisWorkbook.Worksheets("3")
'loop through cells till empty
i = 0

Do While IsEmpty(ws.Range("J" & 3 + i)) < True

If ws.Range("P" & 3 + i) (distance / 1000) And ws.Range("B" & 3
+ i) = 1 Then _
lrods_1.Add ws.Range("J" & CStr(3 + i)), ws.Range("P" & CStr(3
+ i))

If ws.Range("P" & 3 + i) (distance / 1000) And ws.Range("B" & 3
+ i) = 9 Then _
lrods_9.Add ws.Range("J" & CStr(3 + i)), ws.Range("P" & CStr(3
+ i))

i = i + 2 'skip every second
Loop

Debug.Print "after filling dictionaries - the i value was", i - 2
Debug.Print "dictionary info", lrods_9.Count, UBound(lrods_9.Keys)

End Sub

Many thanks,
Tim

On Aug 18, 11:36*am, thorgan wrote:
Dear Group,

I am completely stumped by this. I am filling a dictionary and then
looping over an array made from its keys later. However, if I loop to
the count size of the dictionary I will hit an error. The reason seems
to be that the array that is made via the keys method, is one element
shorter than the dictionary count?

Sample code below:
Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'' *INPUTS HERE ''
Public Const distance As Single = 26.1
Public lrods As Dictionary
Public lrods_1 As Dictionary
Public lrods_9 As Dictionary
Public worst As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''

Sub Identify_lrods()
Dim ws As Worksheet
Dim i As Integer
Set lrods = New Dictionary
Set lrods_1 = New Dictionary
Set lrods_9 = New Dictionary

'look at sheet 3
Set ws = ThisWorkbook.Worksheets("3")
'loop through cells till empty
i = 0

Do While IsEmpty(ws.Range("J" & 3 + i)) < True

* * If ws.Range("P" & 3 + i) (distance / 1000) And ws.Range("B" & 3
+ i) = 1 Then _
* * * * lrods_1.Add ws.Range("J" & CStr(3 + i)), ws.Range("P" & CStr(3
+ i))

* * If ws.Range("P" & 3 + i) (distance / 1000) And ws.Range("B" & 3
+ i) = 9 Then _
* * * * lrods_9.Add ws.Range("J" & CStr(3 + i)), ws.Range("P" & CStr(3
+ i))

* * i = i + 2 'skip every second
Loop

Debug.Print "after filling dictionaries - the i value was", i - 2
Debug.Print "dictionary info", lrods_9.Count, UBound(lrods_9.Keys)

End Sub

Many thanks,
Tim

UPDATE:

A colleague figured this out - the arrays created from dictionaries
seem to start their index at zero. However, something odd came of
that. Later the .keys array was assigned to an array dimensioned as (1
to dictionary.count). No errors occurred, but it seems that the array
was shifted - i.e. filled with, say, 15 keys, but array(15) would
return an error. Odd.

Any thoughts?

Tim

What do Array(0) through Array(14) return?
--
HTH,
Barb Reinhardt



"thorgan" wrote:

On Aug 18, 11:36 am, thorgan wrote:
Dear Group,

I am completely stumped by this. I am filling a dictionary and then
looping over an array made from its keys later. However, if I loop to
the count size of the dictionary I will hit an error. The reason seems
to be that the array that is made via the keys method, is one element
shorter than the dictionary count?

Sample code below:
Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'' INPUTS HERE ''
Public Const distance As Single = 26.1
Public lrods As Dictionary
Public lrods_1 As Dictionary
Public lrods_9 As Dictionary
Public worst As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''

Sub Identify_lrods()
Dim ws As Worksheet
Dim i As Integer
Set lrods = New Dictionary
Set lrods_1 = New Dictionary
Set lrods_9 = New Dictionary

'look at sheet 3
Set ws = ThisWorkbook.Worksheets("3")
'loop through cells till empty
i = 0

Do While IsEmpty(ws.Range("J" & 3 + i)) < True

If ws.Range("P" & 3 + i) (distance / 1000) And ws.Range("B" & 3
+ i) = 1 Then _
lrods_1.Add ws.Range("J" & CStr(3 + i)), ws.Range("P" & CStr(3
+ i))

If ws.Range("P" & 3 + i) (distance / 1000) And ws.Range("B" & 3
+ i) = 9 Then _
lrods_9.Add ws.Range("J" & CStr(3 + i)), ws.Range("P" & CStr(3
+ i))

i = i + 2 'skip every second
Loop

Debug.Print "after filling dictionaries - the i value was", i - 2
Debug.Print "dictionary info", lrods_9.Count, UBound(lrods_9.Keys)

End Sub

Many thanks,
Tim

UPDATE:

A colleague figured this out - the arrays created from dictionaries
seem to start their index at zero. However, something odd came of
that. Later the .keys array was assigned to an array dimensioned as (1
to dictionary.count). No errors occurred, but it seems that the array
was shifted - i.e. filled with, say, 15 keys, but array(15) would
return an error. Odd.

Any thoughts?

Tim

On Aug 18, 1:20*pm, Barb Reinhardt
wrote:
What do Array(0) through Array(14) return?
--
HTH,
Barb Reinhardt



"thorgan" wrote:
On Aug 18, 11:36 am, thorgan wrote:
Dear Group,

I am completely stumped by this. I am filling a dictionary and then
looping over an array made from its keys later. However, if I loop to
the count size of the dictionary I will hit an error. The reason seems
to be that the array that is made via the keys method, is one element
shorter than the dictionary count?

Sample code below:
Option Explicit

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'' *INPUTS HERE ''
Public Const distance As Single = 26.1
Public lrods As Dictionary
Public lrods_1 As Dictionary
Public lrods_9 As Dictionary
Public worst As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''

Sub Identify_lrods()
Dim ws As Worksheet
Dim i As Integer
Set lrods = New Dictionary
Set lrods_1 = New Dictionary
Set lrods_9 = New Dictionary

'look at sheet 3
Set ws = ThisWorkbook.Worksheets("3")
'loop through cells till empty
i = 0

Do While IsEmpty(ws.Range("J" & 3 + i)) < True

* * If ws.Range("P" & 3 + i) (distance / 1000) And ws.Range("B" & 3
+ i) = 1 Then _
* * * * lrods_1.Add ws.Range("J" & CStr(3 + i)), ws.Range("P" & CStr(3
+ i))

* * If ws.Range("P" & 3 + i) (distance / 1000) And ws.Range("B" & 3
+ i) = 9 Then _
* * * * lrods_9.Add ws.Range("J" & CStr(3 + i)), ws.Range("P" & CStr(3
+ i))

* * i = i + 2 'skip every second
Loop

Debug.Print "after filling dictionaries - the i value was", i - 2
Debug.Print "dictionary info", lrods_9.Count, UBound(lrods_9.Keys)

End Sub

Many thanks,
Tim

UPDATE:

A colleague figured this out - the arrays created from dictionaries
seem to start their index at zero. However, something odd came of
that. Later the .keys array was assigned to an array dimensioned as (1
to dictionary.count). No errors occurred, but it seems that the array
was shifted - i.e. filled with, say, 15 keys, but array(15) would
return an error. Odd.

Any thoughts?

Tim- Hide quoted text -

- Show quoted text -

Hi Barb,

thanks for your interest. The array elements are now correctly filled
with the keys. I had defined the array to be array(1 to 15), rather
than dim array(14). So I didn't think that after the
array=dictionary.keys assignment it would work as the latter rather
than the former (or the former with an error).

Kind regards,
Tim