Error due to missing date

[saracat2012]

Hello all,
I have a macro I run for open orders, to do some formatting (see below). It looks for the next workday, adds a row, and formats it in a loop for the next 5 workdays, with a second loop adding text. My issue is that if I've filled all orders the next work day (i.e. on 1/6, 1/7 does not appear next) , and the date disappears from the report, the loop for that date doesn't add a row and format in the correct place. It should look like this:

Instead, it adds a row at the top (since it starts by selecting A1) and formats that. Any ideas on how to correct this?

TIA

Sub FindValue()


Dim rng As Range
Dim cell As Range
Dim d1 As Date
Dim count As Integer

'specify range to look in
Set rng = ActiveSheet.Columns("A:A")



For count = 1 To 6
d1 = Application.WorksheetFunction.WorkDay(Date, count)
On Error Resume Next

'find cell
Set cell = rng.Find(what:=d1, LookIn:=xlFormulas, _
                    lookat:=xlWhole, MatchCase:=False)
                    

'For count = 1 To 5

If cell = d1 Then
    cell.Select
    ActiveCell.EntireRow.Insert
    
    
    ActiveCell.EntireRow.Select
'format row
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
Else

End If
Next count

For count = 1 To 6
On Error Resume Next

'add text

Range("A:A").End(xlDown).Offset(1).Select

If count = 1 Then
    Selection.FormulaR1C1 = "Due Today/Overdue"
    
Else
    Selection.FormulaR1C1 = "Day " & count - 1
    
End If

Next count
    
End Sub

 

[saracat2012]

For those interested, I added an "On Error GoTo" break that adds one day to the "count" in the d1 section. Initially, this broke the "For count = 1 to 6" section, but by adding an If-Then statement for counts greater than or equal to 6 (maximum loops I want completed), it fixed the issue. I'll gladly post the code if anyone's interested.

 

[Peter_SSs]

I'll gladly post the code if anyone's interested.

That would be a good idea and then you could mark that post as the solution. As it is, people searching for a similar problem would have difficulty interpreting just what you mean in post 2, so I have removed the 'Mark as solution' tick from post 2.

 

[saracat2012]

Sorry, I lost visibility of this. Below is the code change I made to make this work.

Sub FindValue()


Dim rng As Range
Dim cell As Range
Dim d1 As Date
Dim count As Integer

'specify range to look in
Set rng = ActiveSheet.Columns("A:A")



For count = 1 To 6
d1 = Application.WorksheetFunction.WorkDay(Date, count)
'error fix HERE
On Error GoTo oops
continue:
'find cell
Set cell = rng.Find(what:=d1, LookIn:=xlFormulas, _
                    lookat:=xlWhole, MatchCase:=False)
                    

'For count = 1 To 6

If cell = d1 Then
    cell.Select
    ActiveCell.EntireRow.Insert
    
    
    ActiveCell.EntireRow.Select
'format row
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With

Else

End If

Next count


'error fix jumps to HERE
oops:
  d1 = Application.WorksheetFunction.WorkDay(Date, count + 1)
  On Error GoTo -1
 
  If count <= 6 Then
    GoTo continue
    Else
 End If
 
 For count = 1 To 6
On Error Resume Next

'add text

Range("A:A").End(xlDown).Offset(1).Select

If count = 1 Then
    Selection.FormulaR1C1 = "Due Today/Overdue"
    
Else
    Selection.FormulaR1C1 = "Day " & count - 1
    
End If

Next count
 
 

 
End Sub