I'm not sure I'm doing this correctly at all but I'm trying to write a subroutine that looks for the first value that meets my criteria, then copies it to another cell, then stops looking for any more values.
To be explicit, I have a column of 18 months in ascending order (oldest to newest) and I'm comparing it to today's date.
- Sep 1st, 2019
- Oct 1st, 2019
- Nov 1st, 2019
- Dec 1st, 2019
- Jan 1st, 2020
- Feb 1st, 2020
- Mar 1st, 2020
- Apr 1st, 2020
- May 1st, 2020
- Jun 1st, 2020
- Jul 1st, 2020
- Aug 1st, 2020
- Sep 1st, 2020
- Oct 1st, 2020
- Nov 1st, 2020
- Dec 1st, 2020
- Jan 1st, 2021
- Feb 1st, 2021
I then want the first month that is after today's date to be copied over to another cell, and then for the Macro to stop searching for more values that meet this criteria.
This is what my code looks like right now.
Sub Show_remaining_months() Dim TodaysDate As Long 'Today's Value Dim MonthCell As Range Dim i As Byte Dim EndHere As Byte Dim RestoreRefStyle As Integer Let RestoreRefStyle = Application.ReferenceStyle Application.ReferenceStyle = xlR1C1 ThisWorkbook.Worksheets("subtotalizer(r-hrs)").Activate Let TodaysDate = Worksheets("subtotalizer(r-hrs)").Range("R1C5").Value ' TodaysDate = 44012 Let EndHere = 23 'Range(R6C3:R23C3) For Each MonthCell In Range("R6C3:R" & (EndHere) & C3) For i = 6 To EndHere ' For i = 6 To 23 ' Which later then becomes i To EndHere. If MonthCell.Value < TodaysDate Then 'Skip i = i + 1 'i = 6 + 1 = 7 Else Let Range(R3C5).Value = MonthCell.Value 'i = i + 1 EndHere = i End If Next i Next MonthCell Application.ReferenceStyle = RestoreRefStyle End Sub I get Error Code 1004: Application-defined or object-defined error
To be honest, I think I'm overthinking this one. I'm new to VBA programming.
