In the range B3:B1000 most of the cells are empty.
B3 always has a value.
Some cells in the range have values.
The values in the range follow a repeating pattern.
The pattern is derived from a repeating series of values in this order; Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
If the value in B3 is Sunday, then the next value in the range will be Monday.
If the value in B3 is Thursday, then the next value in the range will be Friday.
I want a VBA code that will determine the value in B3, then pop up a message and state the next expected value in the message.
The code will then find the next cell that should contain the next expected value and when the non-empty cell is found, pop up a message and state the next expected value in the message.
The code must loop down the range looking for non-empty cells until it gets to B1000.
If the code finds a cell and the value in it is not the expected value, then the pop-up message must state error found and the loop should stop.

The code below accurately determines what the value is in B3. It pops up a message stating the correct next expected value. But when the code finds the next non-empty value which in this instance contains the correct next expected value, it pops up the message Error Found which is incorrect.

Sub FindNextValue()
    Dim rng As Range
    Dim cell As Range
    Dim currentValue As String
    Dim nextValue As String
    Dim pattern As Variant
    Dim i As Integer
    Dim errorFound As Boolean
    
    ’ Set the range where values are present
    Set rng = Range(“B3:B1000”)
    
    ’ Define the repeating pattern
    pattern = Array(“Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”, “Sunday”)
    
    ’ Get the value in B3
    currentValue = Range(“B3”).Value
    
    ’ Find the index of the current value in the pattern
    For i = LBound(pattern) To UBound(pattern)
        If pattern(i) = currentValue Then
            Exit For
        End If
    Next i
    
    ’ Determine the next expected value
    If i = UBound(pattern) Then
        nextValue = pattern(LBound(pattern))
    Else
        nextValue = pattern(i + 1)
    End If
    
    ’ Display message with the next expected value
    MsgBox "Next expected value: " & nextValue
    
    ’ Reset the error flag
    errorFound = False
    
    ’ Find the next non-empty cell in the range
    For Each cell In rng
        If cell.Value <> “” Then
            ’ Check if the value matches the expected value
            If cell.Value = nextValue Then
                ’ Determine the next expected value for the next iteration
                If i = UBound(pattern) Then
                    i = LBound(pattern)
                Else
                    i = i + 1
                End If
                
                nextValue = pattern(i)
                
                ’ Display message with the next expected value
                MsgBox "Next expected value: " & nextValue
                
                ’ Clear contents of the current cell
                cell.ClearContents
                
                Exit For
            Else
                ’ Set the error flag and exit the loop
                errorFound = True
                Exit For
            End If
        End If
    Next cell
   
    ’ Display error message if value doesn’t match
    If errorFound Then
        MsgBox “Error found!”
    End If
End Sub