Quote Originally Posted by xld View Post
Change

        For Each CellaCerc In Sheets("Key Criteria").Range("A2", "A23")
            If (((My_Range.Offset(0, 9) + DayCount - 1) >= CellaCerc.Offset(0, 1)) And ((My_Range.Offset(0, 9) + DayCount - 1) <= CellaCerc.Offset(0, 2))) Then
                Ceiling_Range.Offset(0, 1) = CellaCerc
                Exit For
            End If
        Next
to
       
        For Each CellaCerc In Sheets("Key Criteria").Range("A2", "A23")
        
            If My_Range.Offset(0, 9).Value = "expired" Then
            
                'do nothing
            ElseIf (My_Range.Offset(0, 9).Value + DayCount - 1) >= CellaCerc.Offset(0, 1) And _
                (My_Range.Offset(0, 9).Value + DayCount - 1) <= CellaCerc.Offset(0, 2) Then
                Ceiling_Range.Offset(0, 1) = CellaCerc
                Exit For
            End If
        Next
it seems working, however it gets to a row and stops saying: runtime error 113, type mismatch

i checked the row (after putting a debug.print) and it says row 69 but there is nothing wrong in that one....+

the error sits in this string: For DayCount = 1 To My_Range.Offset(0, 12)

i just tried to check it with a dumm data where after 16 duplications (the specified value in the cell) it stopped....