Retrieving a Collection Item
 

hey guys

I am trying to retrieve the 2nd item in the collection.
The code below adds every item in the specified range to
the collection without any problems, but when it gets to
the last line in the code "FindVal = X.Item(2)" , it says
empty. The 2nd item in the list is "dog" So I want the
msgbox to pop up the value "dog".

Private Sub CommandButton1_Click()
Dim Rng As Range
Dim CL As Object
Dim TestNum As Long
Dim FindVal
Dim X As New Collection

TestNum = 0
Set Rng = Worksheets("Sheet1").Range("A1:A10")
For Each CL In Rng
TestNum = TestNum + 1
If CL.Value < "" Then
X.Add Item:=Inst, key:=CStr(TestNum)
Else
End If
Next

FindVal = X.Item(2)
MsgBox FindVal


Thanks
Todd Huttenstine

Retrieving a Collection Item
 

Todd,

Add Option Explicit at the top of your code module, run it again, and you
will find exactly what is wrong.

--

HTH

RP

"Todd Huttenstine" wrote in message
...
hey guys

I am trying to retrieve the 2nd item in the collection.
The code below adds every item in the specified range to
the collection without any problems, but when it gets to
the last line in the code "FindVal = X.Item(2)" , it says
empty. The 2nd item in the list is "dog" So I want the
msgbox to pop up the value "dog".

Private Sub CommandButton1_Click()
Dim Rng As Range
Dim CL As Object
Dim TestNum As Long
Dim FindVal
Dim X As New Collection

TestNum = 0
Set Rng = Worksheets("Sheet1").Range("A1:A10")
For Each CL In Rng
TestNum = TestNum + 1
If CL.Value < "" Then
X.Add Item:=Inst, key:=CStr(TestNum)
Else
End If
Next

FindVal = X.Item(2)
MsgBox FindVal


Thanks
Todd Huttenstine

Retrieving a Collection Item
 

lol amazing. I cant believe I overlooked that. Thanks!

Todd


-----Original Message-----
Todd,

Add Option Explicit at the top of your code module, run
it again, and you
will find exactly what is wrong.

--

HTH

RP

"Todd Huttenstine"
wrote in message
...
hey guys

I am trying to retrieve the 2nd item in the collection.
The code below adds every item in the specified range to
the collection without any problems, but when it gets to
the last line in the code "FindVal = X.Item(2)" , it
says
empty. The 2nd item in the list is "dog" So I want the
msgbox to pop up the value "dog".

Private Sub CommandButton1_Click()
Dim Rng As Range
Dim CL As Object
Dim TestNum As Long
Dim FindVal
Dim X As New Collection

TestNum = 0
Set Rng = Worksheets("Sheet1").Range("A1:A10")
For Each CL In Rng
TestNum = TestNum + 1
If CL.Value < "" Then
X.Add Item:=Inst, key:=CStr(TestNum)
Else
End If
Next

FindVal = X.Item(2)
MsgBox FindVal


Thanks
Todd Huttenstine


.