Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To find rate of each item from item.xls and to copy price.xls | Excel Discussion (Misc queries) | |||
Item numbers result in item description in next field in Excel | Excel Worksheet Functions | |||
Retrieving an Item from a List that Meets Multiple Criteria | Excel Worksheet Functions | |||
Add Unique item to Collection | Excel Programming | |||
Removing an Item From a Collection | Excel Programming |