Working with Arrays

Although I have been working with Excel for some time, I am still having issues with the arrays.
I would like to create a procedure that copys date from a range (based on two dates values) into an array and then into another worksheet.

So the array to populate is called arrOutput and is of a custom type.  

'Custom Types
Type tPickupOutput
    PickupID                        As Variant
    PickupDate                      As Date
    Name                            As String
    Street                          As String
    StreetNumber                    As String
    City                            As String
End Type


Sub CopyDates(DateFrom As Date, DateTo As Date)
Dim arrPickups()        As Variant
Dim arrCustomers()      As Variant
Dim arrOutput()         As tPickupOutput
Dim CustomerID          As Integer


Dim count               As Long
count = 0

arrPickups = Range("RecyclingTaxiAufträge")
arrCustomers = Range("Kunden")


'count how many items to add to the array
For X = 1 To UBound(arrPickups)
    If arrPickups(X, 2) >= DateFrom And arrPickups(X, 2) <= DateTo Then
        count = count + 1
    End If
Next

ReDim arrOutput(0 To count) As tPickupOutput                 'Public array

For X = 1 To UBound(arrPickups)
        If arrPickups(X, 2) >= DateFrom And arrPickups(X, 2) <= DateTo Then
            arrOutput(X).PickupID = arrPickups(X, 1)
            CustomerID = arrPickups(X, 3)
            arrOutput(X).Name = LookupCustomer(CustomerID, CustomerName)
            arrOutput(X).Street = LookupCustomer(CustomerID, CustomerStreet)
            arrOutput(X).StreetNumber = LookupCustomer(CustomerID, CustomerStreetNumber)
            arrOutput(X).City = LookupCustomer(CustomerID, CustomerCity)
        End If
Next X

For y = 1 To UBound(arrOutput)
    Debug.Print (arrOutput(y).City)
Next y

End Sub

Open in new window


There is an issue with the for loop.
What do I need to change in order to populate the array arrOutput?

The content of the arrOutput will be put into another worksheet.
But for a start, I need to know how to properly populate this dynamic array.

Thanks for your help
sample.xlsm
Massimo ScolaAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
HI,

pls try
Sub CopyDates(DateFrom As Date, DateTo As Date)
Dim arrPickups()        As Variant
Dim arrCustomers()      As Variant
Dim arrOutput()         As tPickupOutput
Dim CustomerID          As Integer


Dim count               As Long
count = 0

arrPickups = Range("RecyclingTaxiAufträge")
arrCustomers = Range("Kunden")


'count how many items to add to the array
For X = 1 To UBound(arrPickups)
    If arrPickups(X, 2) >= DateFrom And arrPickups(X, 2) <= DateTo Then
        count = count + 1
    End If
Next

ReDim arrOutput(0 To count) As tPickupOutput                 'Public array
Y = 0
For X = 1 To UBound(arrPickups)
    
        If arrPickups(X, 2) >= DateFrom And arrPickups(X, 2) <= DateTo Then
            arrOutput(Y).PickupID = arrPickups(X, 1)
            CustomerID = arrPickups(X, 3)
            arrOutput(Y).Name = LookupCustomer(CustomerID, CustomerName)
            arrOutput(Y).Street = LookupCustomer(CustomerID, CustomerStreet)
            arrOutput(Y).StreetNumber = LookupCustomer(CustomerID, CustomerStreetNumber)
            arrOutput(Y).City = LookupCustomer(CustomerID, CustomerCity)
        End If
        Y = Y + 1
Next X

For Y = 0 To UBound(arrOutput)
    Debug.Print (arrOutput(Y).City)
Next Y

Open in new window

Regards
0
 
Massimo ScolaAuthor Commented:
Hi

I get a subscript out of range when I run it when X = 7182.
It is strange as there is a pickup id with that number. Doesn't subscript out of range imply that I am trying to accessing something that is not existent?

I took a screenshot because while x is 7182, something else is 6855?

Thanks for your help

screengrab.PNGsample.xlsm
0
 
Rgonzo1971Commented:
Have you tried my code

arrPickups has the size of your entire list
arrOutput has only the size of the "count"
0
 
Massimo ScolaAuthor Commented:
Hi

Sorry for the delay.

I had to tweak the code a bit and it now works.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.