adding values to variable array VBA

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 useReDim aArray(1 To 1) As SingleIf strFirstName = "henry" Then    aArray(UBound(aArray)) = 123.45    ReDim Preserve aArray(1 To UBound(aArray) + 1) As SingleEnd 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.

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)  NextEnd Sub

I think is better to use the listArray object:

Dim list, name as variantSet 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 nameNext