Last used index in an array

[rowlandrat]

Hi,

How do I create a 1 dimensional array that grows as i add lines of text to it?

Or if I have to set a upper bound on it, how do I then find out which is the last index that contains a value and add the text to the following index?

Thanks

 

[Sektor]

Sub DynamicArray()

    Dim arr() As Variant, i As Integer
    
    ' Define some loop.
    For i = 1 To 100
        ' Some work
        '........
        
        
        ' Resize array.
        ReDim Preserve arr(1 To i)
        arr(i) = i

    Next
    
    Sheet1.Range("A1").Resize(UBound(arr), 1) = WorksheetFunction.Transpose(arr)

End Sub

Sub StaticArray()

    Dim arr(1 To 100) As Variant, i As Integer
    
    ' Define some loop.
    For i = 1 To 100
        ' Some work
        '........
        
        
        ' Use array.
        arr(i) = i

    Next
    
    Sheet1.Range("A1").Resize(UBound(arr), 1) = WorksheetFunction.Transpose(arr)

End Sub

 

[rowlandrat]

What if I have an array declared as being 200 in length. And I only add items to it if they are not already in there. So I want to search the array (looping through the entire 200) and if its not there I want to insert the item at the first unused row. Obviously the first time there is nothing in it so it will add it at the 1st row, but every time after that it needs to know where the first unused row is and insert after it.

Any one got any advice?

 

[kpark91]

Well you are working with an array and you could set restrictions in the range of values in the array so that you can choose a value to display its unused index.

For example, when you have an array of 5 elements.

pos: 0 1 2 3 4
val:  1 2 3 4 -1

In this case, -1 would be the value that identifies that the index was never used or is not being used.
With simple If statements, this can be done.

For n = 0 To UBound(arr) 'assuming arr is your array
    If arr(n) = -1 Then
        arr(n) = elem 'assuming elem is the value you want to insert
    End If
Next n

 

[Sektor]

The code will give the answer "4".

Sub LastIndex()

    Dim arr(1 To 5) As String

    arr(1) = "1"
    arr(2) = "2"
    arr(3) = "3"

    MsgBox WorksheetFunction.Match(vbNullString, arr, 0)

End Sub