Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
Dim lr As Long, r As Range
Dim Data_search As String, i As Integer
Range("B7:M160").ClearContents
Data_search = Sheets("Report").Range("H2")
With Sheets("Data")
    lr = .Range("I" & Rows.Count).End(xlUp).Row
    For Each r In .Range("I2:I" & lr)
        If r.Value = Data_search Then
            Sheets("Report").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(1, -4).Value
            Sheets("Report").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(1, -3).Value
            Sheets("Report").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(1, -2).Value
            Sheets("Report").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(1, -1).Value
            Sheets("Report").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 8).Value = r.Resize(1, 8).Value
        End If
    Next r
End With
End Sub

Code: Select all

'Other code...
            Sheets("Report").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(, -4).Value
            Sheets("Report").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(, -3).Value
            Sheets("Report").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(, -2).Value
            Sheets("Report").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = r.Offset(, -1).Value
            Sheets("Report").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 8).Value = r.Resize(, 8).Value
 'Other code...