I'm looking to export a selection of data from one worksheet to another. To do this, I'm defining an output array equal in size to the amount of rows and columns of relevant entries within the source data. This is so my output is nice and neat without any gaps.
Then, I'm populating the array with the values of those relevant entries found within the original data. Following this, I plan to write the output array from a destination cell in another worksheet and go on from there.
However, I'm stuck on populating the array with values. I've done my best to clean up my code (I like to leave loads of comments so that I understand things. Learning!!) and remove anything too context specific. I think it's probably a really simple error because this code works perfectly elsewhere. The difference is that I've included 5 additional blank rows in the array I wish to output because I want to populate those rows with headers within the eventual output.
Thanks in advance. As I said, I feel like this is probably one of those things that's staring me in the face that someone with more experience will spot instantly *sigh*
Then, I'm populating the array with the values of those relevant entries found within the original data. Following this, I plan to write the output array from a destination cell in another worksheet and go on from there.
However, I'm stuck on populating the array with values. I've done my best to clean up my code (I like to leave loads of comments so that I understand things. Learning!!) and remove anything too context specific. I think it's probably a really simple error because this code works perfectly elsewhere. The difference is that I've included 5 additional blank rows in the array I wish to output because I want to populate those rows with headers within the eventual output.
Code:
Sub TestExport()
Dim TruckRange As Range
Dim Rowcounter As Long
Dim TruckLastRow As Long
Dim TruckNum As Long
Dim TruckArray As Variant
Dim OutputRows As Long
Dim OutputRange As Range
Dim rCell As Range
Dim colUnique As Collection
Dim Days As Long
Dim Columncounter As Long
'Last row:
For Rowcounter = 1 To 300
If Cells(5 + Rowcounter, 4).Value = "" Then
TruckLastRow = Rowcounter + 4
Exit For
End If
Next Rowcounter
'Count distinct number of days in column G
Set colUnique = New Collection
'loop through all selected cells and add to collection
For Each rCell In Worksheets("Reference").Range(Cells(6, 7), Cells(TruckLastRow, 7)) On Error Resume Next
colUnique.Add rCell.Value, CStr(rCell.Value)
On Error GoTo 0
Next rCell
Days = colUnique.Count
'Days = 5
'defining the array SIZE
OutputRows = Days + TruckLastRow
'this is 5 + 31 = 36. The reason there are 5 additional rows is that I'm going to code in the five distinct days
'as headers later, and each header takes up an additional row.
Set TruckRange = Worksheets("Reference").Range(Cells(6, 4), Cells(TruckLastRow, 15))
'Code works up to here. "Truckrange.select" selects D6:O41, which is five rows longer than
'the actual data so as to account for the 5 headers I'm code to write into the output array later
'The following removes gaps in the data
TruckCount = 0
For Rowcounter = 1 To TruckRange.Rows.Count
If TruckRange(Rowcounter, 5).Value <> "" Then
TruckCount = TruckCount + 1 'for every value counted in the 'E' column, the truckcount increases by 1
End If
Next Rowcounter
'Array size is defined, but it needs to be populated with data
ReDim TruckArray(TruckCount, 12)
TruckNum = 0
For Rowcounter = 1 To TruckRange.Rows.Count
If TruckRange(Rowcounter, 5).Value <> "" Then
TruckNum = TruckNum + 1
For Columncounter = 4 To 15 'Get the columncounter to start on column D and count to O. Is this right?
TruckArray(TruckNum, Columncounter) = TruckRange(Rowcounter, Columncounter).Value
'^^^ i.e. the array is equal to the VALUES contained in the previously defined range of data
'However, it's this line that returns a "Subscript out of range" error
Next Columncounter
End If
Next RowcounterThanks in advance. As I said, I feel like this is probably one of those things that's staring me in the face that someone with more experience will spot instantly *sigh*
