Function MonthNum(monthName As String) As Long
MonthNum = Month(DateValue("1" & "/" & monthName & "/" & "2000"))
End Function
Sub SetChartDataWithin90Days()
Dim date90 As String, newestYear As Range, newestMonth As Range, newestDay As Range, i As Long, startingRow As Long, searchMonth As Range, _
searchYear As Range, searchLoop As Long, oldestDate As String, oldestDateMonth As String, oldestDateDay As String
Set newestYear = Range("A" & Rows.Count).End(xlUp)
Set newestMonth = Range("B" & Rows.Count).End(xlUp)
Set newestDay = Range("C" & Rows.Count).End(xlUp)
'Debug.Print newestDay & "/" & MonthNum(newestMonth.Value) & "/" & newestYear
date90 = DateAdd("d", -90, newestDay & "/" & MonthNum(newestMonth.Value) & "/" & newestYear) 'Get date 90 days before the newest
'Debug.Print date90
'Get the oldest date within 90 days preceding the newest date
For i = newestDay.Row To 2 Step -1
'If in second or later loop reset ranges
If Not searchMonth Is Nothing Then
Set searchMonth = Nothing
End If
If Not searchYear Is Nothing Then
Set searchYear = Nothing
End If
'Get the oldest month within 90 days preceding the newest date
searchLoop = i
Do
If Not IsEmpty(Cells(i, "B")) Then
Set searchMonth = Cells(i, "B")
Else 'If month can't be obtained keep searching above row
Set searchMonth = Cells(searchLoop, "B")
searchLoop = searchLoop - 1
End If
Loop While IsEmpty(searchMonth)
'Get the oldest year within 90 days preceding the newest date
searchLoop = i
Do
If Not IsEmpty(Cells(i, "A")) Then
Set searchYear = Cells(i, "A")
Else 'If year can't be obtained keep searching above row
Set searchYear = Cells(searchLoop, "A")
searchLoop = searchLoop - 1
End If
Loop While IsEmpty(searchYear)
'Check if a date in a row is within 90 days preceding the newest date
oldestDateMonth = MonthNum(searchMonth.Value)
oldestDateDay = Cells(i, "C")
If Len(oldestDateMonth) = 1 Then
oldestDateMonth = "0" & oldestDateMonth
End If
If Len(oldestDateDay) = 1 Then
oldestDateDay = "0" & oldestDateDay
End If
oldestDate = oldestDateMonth & "/" & oldestDateDay & "/" & Right(searchYear, 2)
If date90 > oldestDate Then
startingRow = Rows(i + 1).Row
Exit For
End If
'Debug.Print MonthNum(searchMonth.Value) & "/" & Cells(i, "C") & "/" & Right(searchYear, 2)
Next i
'If impossible to trace back to the past 90days
If startingRow = 0 Then
startingRow = 2
End If
'Debug.Print startingRow
'Set chart data
ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=Range(Cells(startingRow, "E"), Cells(newestDay.Row, "F"))
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).XValues = Range(Cells(startingRow, "B"), Cells(newestDay.Row, "C"))
End Sub