MCouture
07-07-2017, 06:41 AM
Hi,
I had this code working last week and then moved a column and something went crazy. I decided to start from scratch today and still can't figure out what I am doing wrong. This is my first VBA so I know it is clunky. I am trying to look up lot values (Material Issued - JobNum) based on the value in cell A1 then list them below the cell. I then need to check those results against a list (Material Issued - JobNum) and get the results (Material Issued - LotNum) added onto the bottom of the first results list, all the way down the results list.
Sub GetMaterial()
j = 1
For i = 2 To Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
If Sheet1.Cells(i, 7).Value = Sheet3.Cells(1, 1) Then
j = j + 1
Sheet3.Cells(j, 1) = Sheet1.Cells(i, 1)
End If
Next i
End Sub
Sub NextLevel()
j = Sheet3.Cells(Rows.Count, 1).End(xlUp).Row
For y = 2 To 300
For i = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
If Sheet3.Cells(y, 1).Value = Sheet1.Cells(i, 7) Then
j = j + 1
Sheet3.Cells(j, 1) = Sheet1.Cells(i, 1)
End If
Next i
Next y
End Sub
I had this code working last week and then moved a column and something went crazy. I decided to start from scratch today and still can't figure out what I am doing wrong. This is my first VBA so I know it is clunky. I am trying to look up lot values (Material Issued - JobNum) based on the value in cell A1 then list them below the cell. I then need to check those results against a list (Material Issued - JobNum) and get the results (Material Issued - LotNum) added onto the bottom of the first results list, all the way down the results list.
Sub GetMaterial()
j = 1
For i = 2 To Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
If Sheet1.Cells(i, 7).Value = Sheet3.Cells(1, 1) Then
j = j + 1
Sheet3.Cells(j, 1) = Sheet1.Cells(i, 1)
End If
Next i
End Sub
Sub NextLevel()
j = Sheet3.Cells(Rows.Count, 1).End(xlUp).Row
For y = 2 To 300
For i = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
If Sheet3.Cells(y, 1).Value = Sheet1.Cells(i, 7) Then
j = j + 1
Sheet3.Cells(j, 1) = Sheet1.Cells(i, 1)
End If
Next i
Next y
End Sub