Const AValues = "1,10,5"
Public A(2) As Integer
Public Sub MyMacro()
Dim vntTemp As Variant
Dim intIndex As Integer
vntTemp = Split(AValues, ",")
For intIndex = 0 To 2
A(intIndex) = vntTemp(intIndex)
Next
Debug.Print A(0) 'will return 1
Debug.Print A(1) 'will return 10
Debug.Print A(2) 'will return 5
End Sub
Dim myArr As Variant
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
myArr = Range("A2:L" & lr).Value
Any sample ? …Any suggestions ?
Sub getarr()
Dim nameList() As Variant
Dim i As Integer
ReDim Preserve nameList(2)
nameList(0) = "AB1CDE Jones John K John K Jones Sr. 123 Main Street Anywhere Mo. 54685"
nameList(1) = "W5HA Jones, Mary P Mary P Jones 1632 4th Ave MyTown Ca. 66859"
nameList(2) = "K3EDR Able, Tim I Tim I Able Jr #2 16th Street North Miami Fl. 50421"
i = InputBox("Enter #", "Query List")
MsgBox (nameList(i))
End Sub
Sub getarr()
Dim nameList() As Variant
Dim w As String
ReDim Preserve nameList(2)
nameList("AB1CDE") = "AB1CDE Jones John K John K Jones Sr. 123 Main Street Anywhere Mo. 54685"
nameList("W5HA") = "W5HA Jones, Mary P Mary P Jones 1632 4th Ave MyTown Ca. 66859"
nameList("K3EDR") = "K3EDR Able, Tim I Tim I Able Jr #2 16th Street North Miami Fl. 50421"
w = InputBox("Enter ID", "Query List")
MsgBox (nameList(w))
End Sub
How do I get this (which works)
Sub getarr_revamped()
Dim nameList$(), S$
nameList = Split("AB1CDE Jones John K John K Jones Sr. 123 Main Street Anywhere Mo. 54685¤" & _
"W5HA Jones, Mary P Mary P Jones 1632 4th Ave MyTown Ca. 66859¤" & _
"K3EDR Able, Tim I Tim I Able Jr #2 16th Street North Miami Fl. 50421", "¤")
S = InputBox(vbLf & "Enter # :", " Query List")
If S > "" Then MsgBox nameList(S)
End Sub
To this (which doesn't work)
Sub getarr2()
Dim V, Dic As New Collection, S$
For Each V In Split("AB1CDE Jones John K John K Jones Sr. 123 Main Street Anywhere Mo. 54685¤" & _
"W5HA Jones, Mary P Mary P Jones 1632 4th Ave MyTown Ca. 66859¤" & _
"K3EDR Able, Tim I Tim I Able Jr #2 16th Street North Miami Fl. 50421", "¤")
Dic.Add V, Split(V)(0)
Next
S = InputBox(vbLf & "Enter ID :", " Query List")
If S > "" Then MsgBox Dic(S)
Set Dic = Nothing
End Sub
Sub TryDictionary()
Dim dict As Object 'Declare the Dictionary object
Set dict = CreateObject("Scripting.Dictionary") 'Create the Dictionary
Dim key, val
key = "W5HA": val = "W5HA Jones, Mary P Mary P Jones 1632 4th Ave MyTown Ca. 66859"
key = "K3EDR": val = "K3EDR Able, Tim I Tim I Able Jr #2 16th Street North Miami Fl. 50421"
key = "AB1CDE": val = "AB1CDE Jones John K John K Jones Sr. 123 Main Street Anywhere Mo. 54685"
key = InputBox("Enter Term", "Enter")
MsgBox val
'Dispose of VBA Dictionary
Set dict = Nothing
End Sub
Sub TryDictionary()
Dim dict As Object 'Declare the Dictionary object
Set dict = CreateObject("Scripting.Dictionary") 'Create the Dictionary
Dim key, val
dict.Add "W5HA", "W5HA Jones, Mary P Mary P Jones 1632 4th Ave MyTown Ca. 66859"
dict.Add "K3EDR", "K3EDR Able, Tim I Tim I Able Jr #2 16th Street North Miami Fl. 50421"
dict.Add "AB1CDE", "AB1CDE Jones John K John K Jones Sr. 123 Main Street Anywhere Mo. 54685"
key = InputBox("Enter Term", "Enter")
val = dict.Item(key)
MsgBox val
'Dispose of VBA Dictionary
Set dict = Nothing
End Sub
Dim Dic As New Dictionary
Sub Demo1()
Dim S$
If Dic.Count = 0 Then
Dic("W5HA") = "W5HA Jones, Mary P Mary P Jones 1632 4th Ave MyTown Ca. 66859"
Dic("K3EDR") = "K3EDR Able, Tim I Tim I Able Jr #2 16th Street North Miami Fl. 50421"
Dic("AB1CDE") = "AB1CDE Jones John K John K Jones Sr. 123 Main Street Anywhere Mo. 54685"
End If
S = InputBox(vbLf & vbLf & "Enter Term :", " Enter")
If Dic.Exists(S) Then MsgBox Dic(S)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dic.RemoveAll
Set Dic = Nothing
End Sub