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.xlsmIf you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Consolidate xl 2010 worksheets with text | 2 | 24 | |
Formula or Macro to determine variance | 17 | 75 | |
Freeze Panes Solution | 6 | 28 | |
Excel VBA When using VLookup | 6 | 29 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
21 Experts available now in Live!