CODEWITHSUNDEEP
arraysvbavariablesdynamic
Trung Tran
Trung Tran

Reputation: 13741

adding values to variable array VBA

I am trying to loop through a table that has a column for "customers" and "dollar amount". If my loop finds a customer called "greg" or "henry" I want to add his "dollar amount" to an array of an unknown size.

Can someone please help me?

Upvotes: 16

Views: 99967

Answers (3)

David Giraldo
David Giraldo

Reputation: 11

I think is better to use the listArray object:

Dim list, name as variant
Set list = CreateObject("System.Collections.Arraylist")

For i = 1 to Last then  ''Loop in the range
    If strName = "Henry" then
        list.Add ValueToAdd  ''add to the list
    End if 
Next

and then you can loop in the list

For Each name in List
    Msgbox name
Next

Upvotes: 1

Pawel
Pawel

Reputation: 11

Private Sub ArrayMy(DataRange)
  Dim DataIndex() As String
  i = 0
  On Error Resume Next
  ReDim DataIndex(0)
  For Each c In DataRange
      DataIndex(i) = c
      i = i + 1
      ReDim Preserve DataIndex(i)
  Next
End Sub

Upvotes: 1

Steve Rindsberg
Steve Rindsberg

Reputation: 14809

If by unknown size, you mean that number of elements is unknown, you could use a dynamic array.

Dim aArray() As Single ' or whatever data type you wish to use
ReDim aArray(1 To 1) As Single
If strFirstName = "henry" Then
    aArray(UBound(aArray)) = 123.45
    ReDim Preserve aArray(1 To UBound(aArray) + 1) As Single
End If

Ubound(aArray) throws an error if the array hasn't been dimensioned, so we start by adding an element to it. That leaves us with an empty element at the end of the text, so your code should account for that. aArray(Ubound(aArray)-1) will give you the last valid element in the array.

Upvotes: 32

Related Questions