Snasui Excel Forum

Code: Select all

Sub Search()
Dim lr As Long, r As Range
Dim Data_search As String
Data_search = Worksheets("Input").Range("D2").Value
 With Sheets("Preview")
        .Range("a2:J500").ClearContents
      End With
      
With Sheets("Data")
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    For Each r In .Range("E2:E" & lr)
        If r.Value = Data_search Then
            Sheets("Preview").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 6).Value = r.Resize(1, 6).Value
        End If
    Next r
End With
End Sub

Code: Select all

'Other code
For Each r In .Range("e2:e" & lr)
    If r.Value = Data_search Then
        Sheets("Preview").Range("a" & Rows.Count) _
            .End(xlUp).Offset(1, 0).Resize(1, 6).Value = _
            r.Offset(0, -4).Resize(1, 6).Value
    End If
Next r
'Other code