Why is my VBA array returning empty cells, unless I specify an index?

Question

I'm just starting out with VBA and am trying to output an array into a range, but when I set the range to the array, I get blank cells. If I do set the range to a specific index like "titlearray(1, 3)" then it does print the correct output.

This is my full code below..

Sub GenerateList()
    
    baseyr = 2019
    mnthct = 1
    mnthyr = InputBox("Actuals up to: (xx/xxxx format)")
    Sheets("Parameters").Cells(4, 2) = mnthyr
    
    yr = Right(mnthyr, 4)
    mnthcols = 12 * (yr - baseyr + 2)
    
    dtarray = Sheet3.Cells(1, 1).CurrentRegion
    dtcols = UBound(dtarray, 2) - LBound(dtarray, 2)-1
    
    totalcols = dtcols + mnthcols
    ReDim titlearray(1, totalcols)
       
    For i = 1 To totalcols     
        If i <= dtcols Then
            titlearray(1, i) = dtarray(1, i) 
        Else
            titlearray(1, i) = mnthct & "/1/" & baseyr
            
            mnthct = mnthct + 1
            If mnthct = 13 Then
                baseyr = baseyr + 1
                mnthct = 1     
            End If    
        End If
    Next    
    'Sheets("Test").Range(Cells(1, 1), Cells(1, totalcols)) = titlearray
       
End Sub

If i do 'Sheets("Test").Range(Cells(1, 1), Cells(1, totalcols)) = titlearray(1,3), it'll print the correct value.. I feel like this is a really simple mistake but I don't know what it is. Thanks and appreciate your help!

It seem your code is quite complicate for simple execution, especially you it is hard to see the redim size of titlearray based on your code, probably show some raw data and your expected output? — Kin Siang
dtcols = UBound(dtarray, 2) - LBound(dtarray, 2) looks like it might be an off-by-one error. Say the UBound is 5 and the LBound is 1. There will be 5 columns, but 5-1 = 4, so dtcols would be 4. If the intention of dtcols is that it counts the number of columns, then you are off by one. — John Coleman
Hi Rory- dtcols is a dynamic variable based on how many columns of data my source has. totalcols is dtcols + # of months of data I want to see. — Jusec
Hi Kin Siang - IE my raw data has the column headers "Property, Property ID, Tenant" and I want to see 2 years of data. dtcols would be 3, mnthcols would be 24, totalcols would be 27. The expected output would just be one row at the top as "Property, Property ID, Tenant, 1/1/2021, 2/1/2021, 3/1/2021, etc." — Jusec

chris neilsen chris neilsen · Accepted Answer · 2021-05-28T20:06:37

When you Redim an Array, by default it's 0 based (but that can be overridden with Option Base 1)

So, your line

ReDim titlearray(1, totalcols)

is the same as

ReDim titlearray(0 To 1, 0 To totalcols)

Change that to

ReDim titlearray(1 To 1, 1 To totalcols)

Why is my VBA array returning empty cells, unless I specify an index?

1 Answers