Finding gaps within rows of numbers

Hello,

Please see attached.

Via macro, I am trying to establish where there is a gap in numbers, signified by a "-". The 2 cases of this are E3 and E5. Note that B7 isn't gap because it is the first numer in the sequence. Nor is L6, as it is the last.

I'd be grateful for some code to generate the TRUE and FALSE values in column A.

Thanks in advance.

Try in A2

=IF(COUNTIF(C2:K2,"-")<>0,TRUE,FALSE)

Drag/Fill Down

Hope this helps

In A2:

PHP Code: 

=countif(C2:K2,"-")>0 


Hello bd528,

The attached workbook has the formulas entered into column "A".

Try this

Sub test()
    Dim MasterLastRow As Long
    MasterLastRow = Range("B" & Rows.Count).End(xlUp).Row
    
    Range("A1").Resize(MasterLastRow, 1).Formula = "=COUNTIF(C1:K1," & Chr(34) & "-" & Chr(34) & ")>0"
End Sub

Assuming your data begins in B1 and Column A is free to use, "MasterLastRow" can be from your call.

Hope this helps

Try this with your new sample sheet

Option Explicit

Sub test()
    Dim MasterLastRow As Long, StartRow As Long
    Dim RowNo As Long, CellNo As Long
    Dim rng As Range
    
    MasterLastRow = Range("K" & Rows.Count).End(xlUp).Row
    StartRow = 12
    For RowNo = StartRow To MasterLastRow
        Set rng = Range("K" & RowNo & ":AH" & RowNo)
        If WorksheetFunction.CountIf(rng, "-") = 24 Then
            Range("J" & RowNo) = False
        Else
            For CellNo = 1 To 21
                If IsNumeric(rng(CellNo)) And rng(CellNo + 1) = "-" And IsNumeric(rng(CellNo + 2)) Then
                    Range("J" & RowNo) = True
                    Exit For
                Else
                    Range("J" & RowNo) = False
                End If
            Next
        End If
    Next
End Sub

Please don't quote whole posts in your replies, the Moderators don't like it..
Only quote a part of the post if it is really needed for clarity

Try this

Option Explicit

Sub test()
    Dim MasterLastRow As Long, StartRow As Long
    Dim NumberOfColumns As Long
    Dim RowNo As Long, CellNo As Long
    Dim rng As Range
    
    MasterLastRow = Range("K" & Rows.Count).End(xlUp).Row
    StartRow = 12
    NumberOfColumns = Range("AH:AH").Column - Range("K:K").Column + 1
    
    For RowNo = StartRow To MasterLastRow
        Set rng = Range("K" & RowNo & ":AH" & RowNo)
        If WorksheetFunction.CountIf(rng, "-") = NumberOfColumns Then
            Range("J" & RowNo) = False
        Else
            For CellNo = 1 To NumberOfColumns - 1
                If IsNumeric(rng(CellNo)) And rng(CellNo + 1) = "-" Then
                    Range("J" & RowNo) = True
                    Exit For
                Else
                    Range("J" & RowNo) = False
                End If
            Next
        End If
    Next
End Sub

Okay, try this

Option Explicit

Sub test()
    Dim MasterLastRow As Long, StartRow As Long
    Dim NumberOfColumns As Long
    Dim RowNo As Long, CellNo As Long
    Dim ctr As Long
    Dim rng As Range
    Dim IsNumber As Boolean
    
    MasterLastRow = Range("K" & Rows.Count).End(xlUp).Row
    StartRow = 12
    NumberOfColumns = Range("AH:AH").Column - Range("K:K").Column + 1
    
    For RowNo = StartRow To MasterLastRow
        Set rng = Range("K" & RowNo & ":AH" & RowNo)
        If WorksheetFunction.CountIf(rng, "-") = NumberOfColumns Then
            Range("J" & RowNo) = False
        Else
            For CellNo = 1 To NumberOfColumns - 1
                If IsNumeric(rng(CellNo)) And rng(CellNo + 1) = "-" Then
                    ctr = ctr + 1
                End If
                If ctr > 0 Then
                    If IsNumeric(rng(CellNo + 1)) Then
                    IsNumber = True
                    Exit For
                    End If
                End If
            Next
                If ctr > 0 And IsNumber Then
                    Range("J" & RowNo) = True
                Else
                    Range("J" & RowNo) = False
                End If
        End If
        ctr = 0
        IsNumber = False
    Next
End Sub

Happy to have helped.