Excel Forum

Code: Select all

Sub Exp()
Dim i As Long
Dim l As Long
Dim r As Long
Application.ScreenUpdating = False
Range("M7:P30").ClearContents
l = Range("b" & Rows.Count).End(xlUp).Offset(1, 0).Row
For i = 1 To l
    r = Range("n" & Rows.Count).End(xlUp).Offset(1, 0).Row
    If LCase(Range("b" & i).Value) = LCase(Range("n6").Value) Then
    Range("n" & r).Value = Range("b" & i).Value
    Range("m" & r).Value = Range("a" & i).Value
    Range("o" & r).Value = Range("c" & i).Value
    Range("p" & r).Value = Range("d" & i).Value
    End If
Next i
Application.ScreenUpdating = True
End Sub

Code: Select all

For i = 7 To l

Code: Select all

If Range("b" & i).Value = Range("n6").Value And Range("a" & i).Value >= Range("m6").Value Then

Code: Select all

Sub Exp()
Dim i As Long
Dim l As Long
Dim r As Long
Application.ScreenUpdating = False
Range("M7:P30").ClearContents
l = Range("b" & Rows.Count).End(xlUp).Offset(1, 0).Row
For i = 7 To l
    r = Range("n" & Rows.Count).End(xlUp).Offset(1, 0).Row
       If Range("b" & i).Value = Range("n6").Value And Range("a" & i).Value >= Range("m6").Value Then
    Range("n" & r).Value = Range("b" & i).Value
    Range("m" & r).Value = Range("a" & i).Value
    Range("o" & r).Value = Range("c" & i).Value
    Range("p" & r).Value = Range("d" & i).Value
    End If
Next i
Application.ScreenUpdating = True
End Sub