Hi,
I have a sheet with in excess of 600,000 rows of data pulled from a database. The data is in date order. I am searching for the first instance of a status 'LU Loading' for each date, ignoring all other instances of 'LU Loading'. My code works for the first instance but does not advance through to the next date, it always goes to the very first instance of 'LU Loading'
I want it to look for 1/1/2017 'LU Loading', copy and paste that row THEN ignore everything until 2/1/2017, find 'LU Loading', copy paste that row then move to 3/1/2017.
Dates in Col A Status in Col J
in my debug.print I can see that the dates are advancing as I want but not the selection. How do I get LoadStartRow to begin its search from the NextRow value?
Cut down sheet and code attached.
Thanks in advance.
Sub FindFirst()
Dim sht1 As Worksheet
'Dim FoundYou As Range
Dim wsResults As Worksheet
Dim curLastRowData As Long
Dim NextRow As Range
Dim NextDate As Date
Dim d As Date
Dim startDate As Date, endDate As Date
Dim i As Integer
startDate = DateValue("1/1/2017")
endDate = DateValue("4/26/2017")
Set sht1 = Sheet1
Set wsResults = Sheet2
NextDate = startDate
With sht1
For d = startDate To endDate
Debug.Print "Iteration " & i & ":" & vbTab & d
i = i + 1
Set NextRow = sht1.Range("A:A").Find(what:=CDate(NextDate))
Debug.Print NextRow
'find first instance of LU Loading and copy row
Set LoadStartRow = .Cells.Find(what:="LU Loading")
LoadStartRow.EntireRow.Copy
'Find last empty row in results worksheet and paste results
curLastRowData = wsResults.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).row
wsResults.Range("A" & curLastRowData).PasteSpecial xlPasteValuesAndNumberFormats
'add 1 to the date to advance the loop
NextDate = DateAdd("d", 1, startDate)
Next
End With
End Sub
Display More