VBA Excel - 2D array data posting a row & column of 0's

[Mozzie_Kh]

I have a 2D array that stores data from a table after running through the calculations.

I have managed to get the data tabulated and am currently trying to paste the data onto a second excel sheet.

I have managed to apply a brute force method however this isn't practical when the sheet has to get updated with more data, I have been using the UBound code to paste the data, but when I do I keep getting an extra column & row 0's before the data is pasted. I am not sure where the data is coming from.

Initial declaration as follows:
Dim Project_Data() As Integer
Set Output = Sheet2

Then below it's updated for data calculation:
ReDim Project_Data(6, Week_Count)


Code to Paste onto excel sheet:
Dim Destination As Range
Set Destination = Output.Range("B20").Resize(UBound(Project_Data, 1) + 1, UBound(Project_Data, 2))
Destination.Value = Project_Data


Output: (the #'s in red shouldn't be there)

0	0	0	0	0	0	0	0	0	0	0
0	5	6	6	8	9	9	10	10	14	12
0	0	0	0	0	0	0	0	0	0	0
0	1	1	1	0	0	0	0	0	1	2
0	0	0	0	0	0	1	2	3	3	3
0	0	0	0	0	1	0	0	0	0	0
0	0	0	0	0	0	1	1	0	1	1

 

[Fluff]

Hi & welcome to MrExcel.
It would help if you posted all the code, as the problem probably comes from how the array is being populated. How to Post Your VBA Code

 

[Mozzie_Kh]

Please see full code below:

Sub Vacation_Project_Filter()
    Dim table As ListObject
    Dim c As Integer, d As Integer
    Dim Vacation_Count As Integer 'count of vacation per week
    Dim Week_Count As Integer, Emp_Count As Integer
    Dim Project_Data() As Integer
    Dim Weekly_Table(1 To 135) As Integer
    
    Set table = Sheet5.ListObjects("Table4")
    Set Output = Sheet2
    Week_Count = Round((table.ListColumns.Count - 7) / 7, 0)
    
    ReDim Project_Data(6, Week_Count) 
    
    c = 1
    d = 7 '# of days per week to add the vacation days
    
    For z = 1 To table.ListRows.Count
        For a = 1 To Week_Count
            For c = c To d
                If table.DataBodyRange(z, c + 7).Value = "V" Or table.DataBodyRange(z, c + 7).Value = "v" Then
                    Vacation_Count = Vacation_Count + 1
                End If
            Next c
             
            If table.DataBodyRange(z, 2).Value = "PSS-H145" Then
                If Vacation_Count > 2 Then
                    Weekly_Table(a) = Weekly_Table(a) + 1
                    Project_Data(1, a) = Project_Data(1, a) + 1
                End If
            ElseIf table.DataBodyRange(z, 2).Value = "THC" Then
                If Vacation_Count > 2 Then
                    Weekly_Table(a) = Weekly_Table(a) + 1
                    Project_Data(2, a) = Project_Data(2, a) + 1
                End If
            ElseIf table.DataBodyRange(z, 2).Value = "RSAF-MLU" Then
                If Vacation_Count > 2 Then
                    Weekly_Table(a) = Weekly_Table(a) + 1
                    Project_Data(3, a) = Project_Data(3, a) + 1
                End If
            ElseIf table.DataBodyRange(z, 2).Value = "RSAF-H215M" Then
                If Vacation_Count > 2 Then
                    Weekly_Table(a) = Weekly_Table(a) + 1
                    Project_Data(4, a) = Project_Data(4, a) + 1
                End If
            ElseIf table.DataBodyRange(z, 2).Value = "RSNF" Then
                If Vacation_Count > 2 Then
                    Weekly_Table(a) = Weekly_Table(a) + 1
                    Project_Data(5, a) = Project_Data(5, a) + 1
                End If
            Else
                If Vacation_Count > 2 Then
                    Weekly_Table(a) = Weekly_Table(a) + 1
                    Project_Data(6, a) = Project_Data(6, a) + 1
                End If
                
            End If
            
            c = c
            d = d + 7
            Vacation_Count = 0
        Next a
            
        c = 1
        d = 7
    Next z
    
    Dim Destination As Range
    Set Destination = Output.Range("B20").Resize(UBound(Project_Data, 1) + 1, UBound(Project_Data, 2)) 'in UBound, 1 = count of columns & 2 = count of rows
    Destination.Value = Project_Data
End Sub

 

[Mozzie_Kh]

I need to make a change to the above code, how can I edit?

 

[Fluff]

Thanks for that, by default your Project_Data array is base 0 but you are treating it as Base 1, try

ReDim Project_Data(1 to 6, 1 to Week_Count)

although this may need further tweaking.

 

[Mozzie_Kh]

No that seemed to have worked, thank you very much. I guess by the change it starts the array at 1 whereas before it was starting with 0 as a placeholder.

 

[Fluff]

You're welcome & thanks for the feedback.