0

I have a list of 2 columns that A has a random number associated with it and B has a string item. So i was thinking I could make any Arraylist of arrays, but i would like to make the name of the Array the number so it can quickly be referenced. I've tried

Dim row As Integer
Dim arrList As Object
Dim arr As Variant

arrList = CreateObject("System.Collections.ArrayList")

row = 1
While Not IsEmpty(ActiveSheet.Range("A" & row))
ReDim arr(0 To 1) As String
arr(0) = ActiveSheet.Range("A" & row)
arr(1) = ActiveSheet.Range("B" & row)
arrList.Add (arr)

row = row + 1
Wend

Is there a quick way to search ArrList(arr(0)?

levif1
  • 65
  • 2
  • 8
  • 1
    You can't programmatically name a variable. It has to be a literal string declared at compile time – SierraOscar Dec 04 '15 at 15:16
  • So is there any other way of doint this other than adding A, and B to a single array. then adding that array to arrayList? And calling on arr(0) to look at A column. – levif1 Dec 04 '15 at 15:28
  • When you say "arrayList" are you referring to an array of arrays - or an actual "arrayList" object? – SierraOscar Dec 04 '15 at 15:32
  • an Arraylist object ill edit my code to reflect what ive changed it to. – levif1 Dec 04 '15 at 15:37
  • my only problem is. I need a way to see if arrList contains a specific number. I dont think i can do that like i have it right now because ill have to look at each arr(0) to see taht – levif1 Dec 04 '15 at 15:40
  • 1
    It looks like you want the data structure known as a Map, which in general in the Microsoft world is referred to as a Dictionary. See [this](http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure) for more – AakashM Dec 04 '15 at 15:52
  • mk to use one (had to look this up) but i would create a dictionary then .Add "Pizza", 1000. How would i search for the 1000 part. Ive never learned about dictionary's before – levif1 Dec 04 '15 at 16:09

1 Answers1

0

You probably should be using the Dictionary object (https://support.microsoft.com/en-us/kb/187234) and (Does VBA have Dictionary Structure?).

You can use .Exists to check if your key/item exists in the Dictionary or not.

Dim dctMyList As Dictionary
Dim nCount As Integer
Dim sKey As String

Set dctMyList = New Dictionary

For nCount = 1 To 5
  sKey = "Fruit_" & nCount
  dctMyList.Add sKey, Choose(nCount, "Apple", "Banana", "Date", "Fig", "Pear")
Next nCount

If dctMyList.Exists("Fruit_2") Then
  Debug.Print dctMyList.Items(1)
Else
  ' Do something else
End If

Note that you can use .Items to access the contents, which is a zero-based index. So in the above example, "Banana" is the second item added to the Dictionary, but has an index of 1.

Community
  • 1
  • 1
Simon Wray
  • 192
  • 4
  • 12
  • Awesome thank you. So i made 2 dictionaries because i have 2 different sets of data. I use the .Exists it works, but I would like to use the Keys() fuction instead of looking for the item it tells me to it goes to the index. For example one of my items i wanna look for is 1002, which is actually index 2, When i search for 1002 it takes me to the key in index 1002. Is there a way to fix this? – levif1 Dec 08 '15 at 19:09
  • I'm not exactly sure what you're trying to achieve... Using `Dict.Keys` will return an Array of all the Key values in the Dictionary. Are you trying to get an Item from it's Key? or a Key for an Item? I suspect you are trying to get a specific Item out of the Dictionary based on it's Key value. In which case you want `Dict.Item(YourKeyValue)`. So in my above example, `Debug.Print dctMyList.Item("Fruit_5")` would return "Pear". n.b. `.Item` and `.Items` are different and do different things. – Simon Wray Dec 08 '15 at 21:13
  • Sorry. Im trying to get a Key value from its Item, But my items are integer. So when i do .Keys(activesheet.range("A" & x)) it returns the key at index "A"x instead of the key at item "A"x. – levif1 Dec 08 '15 at 21:46
  • It sounds like you are not really understanding the Dictionary object and I'm struggling to make sense of what you're actually trying to do. If you're trying to get a Key from it's Item, then it sounds like you've loaded your Dictionary the wrong way round! i.e. your Keys are used to help find your Items. If not, then you cannot directly get a Key from an Item (items may not be unique), but instead you can loop through all the Keys looking for one with a matching Item: `For Each sMyStringKey In dctMyDict.Keys() Range("A" & sMyStringKey).Value = dctMyDict.Item(sMyStringKey) Next` – Simon Wray Dec 10 '15 at 13:06
  • Also see this: http://www.mrexcel.com/forum/excel-questions/660596-scripting-dictionary-exits-keys-items-etc.html – Simon Wray Dec 10 '15 at 13:09