Excel VBA - My dynamic array only contains the last value that's added

Question

I'm trying to populate a dynamic array and it does work in terms of getting the right amount of values.
for example it needs to have 3 values it has 3 values but only the last one is like a real value.

To clear that up, This it what that array would look like:
Array("", "", "actual string value")
So the first 2 values are empty.

I can't figure out what I'm doing wrong. This is the code:

    Dim LinkSheet As Worksheet
    Set LinkSheet = ThisWorkbook.Sheets("Resources")
    Dim LinkNameRange As Range
    Set LinkNameRange = LinkSheet.Range("Table4[Resource Name]")

    Dim i As Integer
    Dim PlannedProjArr() As String
    i = 0

   'loop through all links and find the projects that the chosen employee is linked to

    For Each linkname In LinkNameRange
        If linkname = Employee Then
            ReDim PlannedProjArr(i)
            PlannedProjArr(i) = linkname.Offset(, -1).Value
            i = i + 1
        End If
    Next linkname

    'test if array works
    For x = 0 To UBound(PlannedProjArr)
        Debug.Print PlannedProjArr(x)
    Next x

It could even be that I'm testing it wrong or something. Any help is appreciated.
Thanks in advance!

Answer 1

you need to use the preserve keyword with your redim statement to keep the values that were added previously

Preserve: Keyword used to preserve the data in an existing array when you change the size of the last dimension.