Using Loops and Arrays

[MCouture]

Hi everyone. I am very slowly trying to learn how to use Macros and VBA. I am trying to lookup values that match a user input (x), store the column next to the matching value (y) and then print a list of the y. I have the list returning correctly in the VBA entry screen. I just can't figure out how to print the list that is created. I think I need to store them as an array and then print the array.


Sub GetMaterial()


For i = 2 To 1048575
If Sheet4.Cells(i, 2).Value = Sheet1.Cells(1, 1) Then
Debug.Print Sheet4.Cells(i, 4)
End If

Next i

End Sub

[mdmackillop]

Best to find the last row to be processed to avoid the code looping 1 million + times.

Sub GetMaterial()
Set sh = Sheets.Add
For i = 2 To Sheet4.Cells(Rows.Count, 2).End(xlUp).Row
If Sheet4.Cells(i, 2).Value = Sheet1.Cells(1, 1) Then
j = j + 1
sh.Cells(j, 1) = Sheet4.Cells(i, 4)
End If
Next i
sh.PrintOut
End Sub

[SamT]

Option Explicit

Sub GetMaterial()
Dim arr1, arr4, arrPrint 'Default of undeclared Type is Variant, ie: Dim arr1 As Variant
Dim i As Long

With Sheet1
arr1 = .Range(.Cells(2, "A"), .Cells(Rows.Count, "A").End(xlUp)).Value 'From Cell(2) to bottom used cell
End With
With Sheet2
arr4 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Value
End With
ReDim arrPrint(1) 'Make it a 1 "slot" array

For i = LBound(arr1) To WorksheetFunction.Min(UBound(arr1), UBound(arr4)) 'Whichever is the smallest Array
If arr1(i) = arr4(i) Then
arrPrint(UBound(arrPrint)) = arr1(i)
ReDim Preserve arrPrint(UBound(arrPrint) + 1) 'Will leave one empty "slot" at the end of the Array
End If
Next i

ReDim Preserve arrPrint(UBound(arrPrint) - 1) 'Get rid of the empty "slot"
Debug.Print arrPrint 'EDit to work
End Sub

[MCouture]

Thank you so much. I had tried to get the formula for just going to the end of the list to work but I was doing something wrong :)