Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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
Join the community of 500,000 technology professionals and ask your questions.