Sid
Private Sub CommandButton1_Click()
Dim aCell As Range
Dim SearchDate As Date, StartDate As Date, EndDate As Date
Dim lastRow As Long, i As Long, outputRow As Long
Range("H5:J" & Rows.Count).ClearContents
SearchDate = Range("I1").Value
Set aCell = Columns(1).Find(What:=SearchDate, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
StartDate = aCell.Offset(, 1).Value
EndDate = aCell.Offset(, 2).Value
lastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
If (Range("D" & i).Value = StartDate Or Range("D" & i).Value > StartDate) And _
(Range("D" & i).Value = EndDate Or Range("D" & i).Value < EndDate) Then
If Range("E" & i).Value = Range("I2").Value Then
outputRow = Range("H" & Rows.Count).End(xlUp).Row + 1
Range("H" & outputRow).Value = Range("D" & i).Value
Range("I" & outputRow).Value = Range("E" & i).Value
Range("J" & outputRow).Value = Range("F" & i).Value
End If
End If
Next i
Else
MsgBox "Not Found"
End If
End Sub
Private Sub CommandButton1_Click()
Dim aCell As Range
Dim SearchDate As Date, StartDate As Date, EndDate As Date
Dim lastRow As Long, i As Long, outputRow As Long
Range("H5:J" & Rows.Count).ClearContents
SearchDate = Range("I1").Value
Set aCell = Columns(1).Find(What:=SearchDate, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
StartDate = aCell.Offset(, 1).Value - 1
EndDate = aCell.Offset(, 2).Value
lastRow = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
If (Range("D" & i).Value = StartDate Or Range("D" & i).Value > StartDate) And _
(Range("D" & i).Value = EndDate Or Range("D" & i).Value < EndDate) Then
If Range("E" & i).Value = Range("I2").Value Then
outputRow = Range("H" & Rows.Count).End(xlUp).Row + 1
Range("H" & outputRow).Value = Range("D" & i).Value
Range("I" & outputRow).Value = Range("E" & i).Value
Range("J" & outputRow).Value = Range("F" & i).Value
End If
End If
Next i
Else
MsgBox "Not Found"
End If
End Sub
File-A.xlsmTitle | # Comments | Views | Activity |
---|---|---|---|
AutoFilter Delete not keeping Headers? | 2 | 12 | |
Need an Excel cell value to freeze once entered | 8 | 29 | |
Excel 2003 Conditional Formatting for Jan 1 | 3 | 13 | |
Top 10 with duplicates in Excel Table | 4 | 4 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
15 Experts available now in Live!