Basic Array Question VBA

[JumboCactuar]

Hi,
im currently trying to learn about VBA arrays so if you know any good learning resources i would appreciate it.

With the following below, how can i store or update the values assigned to an IDNO in an array and make it available to other modules.

So if:
Range("B3") = 7
and
Range("C3") = 2171

this gets stored, or updated if 7 is already in array

then entering 7 in Range("B9") and running the macro should output the stored value for IDNO: 7

Appreciate any help

 

[DanteAmor]

I give you an example.

You must first run the macro "Test_Update_Array" to load the data into memory in the array.
Then run the macro "Test_Display_Array".

Dim dict As Scripting.Dictionary   'Global variable at the beginning of all code


Sub [COLOR=#0000ff]Test_Update_Array[/COLOR]()
  Set dict = CreateObject("scripting.dictionary")
  If Not dict.Exists(Range("B3").Value) Then
    dict.add Key:=Range("B3").Value, Item:=Range("C3").Value
  End If
End Sub


Sub [COLOR=#008000]Test_Display_Array[/COLOR]()
  Dim wDat As Variant
  If dict.Exists(Range("B9").Value) Then
    wDat = dict(Range("B9").Value)
    Range("C9").Value = wDat
  End If
End Sub

 

[JumboCactuar]

Thankyou @DanteAmor I will try this

Though I thought I could do something like:

Dim IdNumber(999) 'store up to 1000 values
i = Range("B3").value

IdNumber(i) = Range("C3").value

 

[DanteAmor]

Thankyou @DanteAmor I will try this

Though I thought I could do something like:

Dim IdNumber(999) 'store up to 1000 values
i = Range("B3").value

IdNumber(i) = Range("C3").value

There are several ways to store arrangements, I just gave you an example.

 

[DanteAmor]

Another example ussing Collection:

Dim idNumber As New Collection
Sub Example2()
  Set idNumber = Nothing
  idNumber.add Item:=Range("C3").Value, Key:=Range("B3").Text
 End Sub


Sub Dspy_example2()
  [C9] = idNumber.Item(Range("B9").Text)
End Sub

 

[DanteAmor]

Example 3

Dim arr()
Sub array_3()
  Dim n As Long
  n = Range("B3")
  ReDim arr(n)
  arr(n) = Range("C3")
End Sub


Sub display_3()
  [C9] = arr(Range("B9"))
End Sub

 

[JumboCactuar]

Thanks @DanteAmor
Example 3 seems to be on the right track

instead of Redim arr
i used Sub array_3(1000)

as if the range("B9") isnt found in the array it throws an error

is there any problem with setting the array size that high?

------------------------------------------

Next im trying to figure how to store IDNO, then a sum of values for the IDNO

like:

with output:

If i understand correctly this needs a 2D array but im unsure how to loop and store for each IDNO

 

[DanteAmor]

What is the goal?
What you need to do, maybe an array is not necessary.

Example 4

Sub ex4()
  Dim a(), i As Long
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row)
  For i = LBound(a) To UBound(a)
    MsgBox "COL A" & vbTab & "COL B" & vbTab & "COL C" & vbCr & _
          a(i, 1) & vbTab & a(i, 2) & vbTab & a(i, 3)
  Next
End Sub

 

[JumboCactuar]

What is the goal?
What you need to do, maybe an array is not necessary.

Example 4

Sub ex4()
  Dim a(), i As Long
  a = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row)
  For i = LBound(a) To UBound(a)
    MsgBox "COL A" & vbTab & "COL B" & vbTab & "COL C" & vbCr & _
          a(i, 1) & vbTab & a(i, 2) & vbTab & a(i, 3)
  Next
End Sub

Thanks
The goal is to store each unique IDNO, along with their name and a TOTAL of their values

 

[DanteAmor]

Thanks
The goal is to store each unique IDNO, along with their name and a TOTAL of their values

Ok, you want to store them, but what do you want to store them for, what are you going to use them for?