Return multiple rows of data in vba

Question

I have a row such as the following:

Destination: Part:
04586        06509269AA

I want to look up this data in another sheet that has multiple rows of the same data with extra information

Destination: Part:       Package:
04586        06509269AA  656665
04586        06509269AA  213226

and return the full data to a new sheet.

I tried doing an index, match, using a key for the data set but it crashes excel due to how much rows of data I actually pull in my query, and also I would need to find a way to increment rows in the new sheet for how many rows of data there actually is.

Any ideas of what I can try in VBA to create this report?

Answer 1

If I am understanding you are trying to get all the "Package values" based on "Part" and "Parameter". I would run a while loop with a for loop inside that iterates at a match and stores in an array that gets bigger using reDim Preserve.

example input with output pasted onto G column

Sub example()

Dim rower, destination, packageCount As Integer
Dim Package() As Variant
Dim part As String

destination = 4586
part = "06509269AA"
rower = 0
packageCount = 0

Sheets("Sheet1").Activate
Range("B3").Activate

Do While ActiveCell.Offset(rower) <> ""
    If ActiveCell.Offset(rower) = destination And ActiveCell.Offset(rower, 1) = part Then
        packageCount = packageCount + 1
        ReDim Preserve Package(packageCount + 1)
        Package(packageCount) = ActiveCell.Offset(rower, 2)
    End If
    rower = rower + 1
Loop

Range("g2").Activate

For i = 0 To UBound(Package)
    ActiveCell.Offset(i) = Package(i)
Next i

End Sub