Good Evening,
I was hoping someone could assist me with this code to make it more efficient. I have a sheet named data where column A has an ID number, I search this in Sheet 3 to pull the data in which works but I’m having an issue. Sometimes depending on my ID search I may need to pull in 4 rows, sometimes 6 or 8. At the moment I’m getting 8 rows regardless. I know why but don’t know how to ensure I just search for the ID number and not carry on the rows below.
Thanks in advance for any advice.
Sub Searchdata()
Dim Byrow As Long
Dim count As Integer
Dim Rng As Range
Byrow = Sheets(“Data”).Cells(Rows.count, 1).End(xlUp).Row
For x = 2 To Byrow
If Sheets(“Data”).Cells(x, 1) = Sheet3.Range(“B3”) Then ‘Machine Order ID
‘Row1
Sheet3.Range(“B14”) = Sheets(“Data”).Cells(x, 8).Offset(1, 0)
Sheet3.Range(“B15”) = Sheets(“Data”).Cells(x, 9).Offset(1, 0)
Sheet3.Range(“C15”) = Sheets(“Data”).Cells(x, 10).Offset(1, 0)
Sheet3.Range(“D15”) = Sheets(“Data”).Cells(x, 11).Offset(1, 0)
Sheet3.Range(“E15”) = Sheets(“Data”).Cells(x, 12).Offset(1, 0)
Sheet3.Range(“F15”) = Sheets(“Data”).Cells(x, 13).Offset(1, 0)
‘Row2
Sheet3.Range(“A16”) = Sheets(“Data”).Cells(x, 8).Offset(2, 0)
Sheet3.Range(“B16”) = Sheets(“Data”).Cells(x, 9).Offset(2, 0)
Sheet3.Range(“C16”) = Sheets(“Data”).Cells(x, 10).Offset(2, 0)
Sheet3.Range(“D16”) = Sheets(“Data”).Cells(x, 11).Offset(2, 0)
Sheet3.Range(“E16”) = Sheets(“Data”).Cells(x, 12).Offset(2, 0)
Sheet3.Range(“F16”) = Sheets(“Data”).Cells(x, 13).Offset(2, 0)
‘Row3
Sheet3.Range(“A17”) = Sheets(“Data”).Cells(x, 8).Offset(3, 0)
Sheet3.Range(“B17”) = Sheets(“Data”).Cells(x, 9).Offset(3, 0)
Sheet3.Range(“C17”) = Sheets(“Data”).Cells(x, 10).Offset(3, 0)
Sheet3.Range(“D17”) = Sheets(“Data”).Cells(x, 11).Offset(3, 0)
Sheet3.Range(“E17”) = Sheets(“Data”).Cells(x, 12).Offset(3, 0)
Sheet3.Range(“F17”) = Sheets(“Data”).Cells(x, 13).Offset(3, 0)
‘Row4
Sheet3.Range(“A18”) = Sheets(“Data”).Cells(x, 8).Offset(4, 0)
Sheet3.Range(“B18”) = Sheets(“Data”).Cells(x, 9).Offset(4, 0)
Sheet3.Range(“C18”) = Sheets(“Data”).Cells(x, 10).Offset(4, 0)
Sheet3.Range(“D18”) = Sheets(“Data”).Cells(x, 11).Offset(4, 0)
Sheet3.Range(“E18”) = Sheets(“Data”).Cells(x, 12).Offset(4, 0)
Sheet3.Range(“F18”) = Sheets(“Data”).Cells(x, 13).Offset(4, 0)
‘Row5
Sheet3.Range(“A19”) = Sheets(“Data”).Cells(x, 8).Offset(5, 0)
Sheet3.Range(“B19”) = Sheets(“Data”).Cells(x, 9).Offset(5, 0)
Sheet3.Range(“C19”) = Sheets(“Data”).Cells(x, 10).Offset(5, 0)
Sheet3.Range(“D19”) = Sheets(“Data”).Cells(x, 11).Offset(5, 0)
Sheet3.Range(“E19”) = Sheets(“Data”).Cells(x, 12).Offset(5, 0)
Sheet3.Range(“F19”) = Sheets(“Data”).Cells(x, 13).Offset(5, 0)
‘Row6
Sheet3.Range(“A20”) = Sheets(“Data”).Cells(x, 8).Offset(6, 0)
Sheet3.Range(“B20”) = Sheets(“Data”).Cells(x, 9).Offset(6, 0)
Sheet3.Range(“C20”) = Sheets(“Data”).Cells(x, 10).Offset(6, 0)
Sheet3.Range(“D20”) = Sheets(“Data”).Cells(x, 11).Offset(6, 0)
Sheet3.Range(“E20”) = Sheets(“Data”).Cells(x, 12).Offset(6, 0)
Sheet3.Range(“F20”) = Sheets(“Data”).Cells(x, 13).Offset(6, 0)
‘Row7
Sheet3.Range(“A21”) = Sheets(“Data”).Cells(x, 8).Offset(7, 0)
Sheet3.Range(“B21”) = Sheets(“Data”).Cells(x, 9).Offset(7, 0)
Sheet3.Range(“C21”) = Sheets(“Data”).Cells(x, 10).Offset(7, 0)
Sheet3.Range(“D21”) = Sheets(“Data”).Cells(x, 11).Offset(7, 0)
Sheet3.Range(“E21”) = Sheets(“Data”).Cells(x, 12).Offset(7, 0)
Sheet3.Range(“F21”) = Sheets(“Data”).Cells(x, 13).Offset(7, 0)
‘Row8
Sheet3.Range(“A22”) = Sheets(“Data”).Cells(x, 8).Offset(8, 0)
Sheet3.Range(“B22”) = Sheets(“Data”).Cells(x, 9).Offset(8, 0)
Sheet3.Range(“C22”) = Sheets(“Data”).Cells(x, 10).Offset(8, 0)
Sheet3.Range(“D22”) = Sheets(“Data”).Cells(x, 11).Offset(8, 0)
Sheet3.Range(“E22”) = Sheets(“Data”).Cells(x, 12).Offset(8, 0)
Sheet3.Range(“F22”) = Sheets(“Data”).Cells(x, 13).Offset(8, 0)
‘Customer/Machine Details
Sheet3.Range(“B7”) = Sheets(“Data”).Cells(x, 16) ‘Customer
Sheet3.Range(“B8”) = Sheets(“Data”).Cells(x, 2) ‘Department
Sheet3.Range(“B9”) = Sheets(“Data”).Cells(x, 4) ‘Manu
Sheet3.Range(“B10”) = Sheets(“Data”).Cells(x, 5) ‘Model
Sheet3.Range(“B11”) = Sheets(“Data”).Cells(x, 6) ‘Plant ID
End If
Next x
End Sub
Thanks,
Ryan
