Offset Loop based on address cell (row) value

[Toffee]

Hi All,
I have a massive headache with trying to find solution to my problem. I have a massive report in which I'm analyzing frequency of deliveries.
Please find attached file.

I have marked with comment in which part of the code i think the offset loop will have to go. PLEASE HELP!

Sub MultipleSearch()

    ' Get name to search
    Dim name As String: name = "Gap"
    Dim Startt
    Dim Endd
    ' Get search range
    Dim rgSearch As Range
    Set rgSearch = Range("BT6:EH647")

    Dim cell As Range
    Set cell = rgSearch.find(name)

    ' If not found then exit
    If cell Is Nothing Then
        Debug.Print "Not found"
        Exit Sub
    End If

    ' Store first cell address
    Dim firstCellAddress As String
    firstCellAddress = cell.Address

    ' Find all cells containing 0
    Do
     Range("EF2") = cell.Address
        Set cell = rgSearch.FindNext(cell)
     Range("EG2") = cell.Address
     
     
     
     Range("EH2").Select
     Selection.Copy
     
     
     Range("EI6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ApplicationCutCopyMode = False
   
 'some were here or after the final loop i would like to add that will
make sure that cell EH2 will be copied to row below because it found every possible instance in the current row
   
   
     Loop While firstCellAddress <> cell.Address

End Sub

Alternatively I'm open to suggestions how to add numbers between "Gap" word resetting count after nex Gap word example:
"Gap 1 Gap 1 1 Gap" will result in having 1 in one cell and 2 in the next one

Kind Regards
Toffee

 

[Luke M]

Hi Toffee,

Can you explain in more detail what is going on overall? Not verbatim what you're doing in code, but what overall goal you're trying to accomplish? As is, I'm not sure what your initial state is, or what the end goal is.

 

[Toffee]

Hi
the issue ive got i would like to sum all the numbers between word "gap" but resetting the sum after the last word.

so Gap 1 Gap will return 1 in cell EJ6
Gap 1 1 Gap will return 2 in cell EK6
after summing all the numbers in row 6 the loop will offset past part of the macro to row below

i will post rest of the replys on the thread if it's easier
Many thanks

 

[Toffee]

when looking at row 6 for part code x

 

[Luke M]

Here's the script to show numbers like you described.

Sub NewCheck()
    Dim myRow As Range
    Dim rngNum As Range
    Dim r As Range
    Dim colCount As Long
    Dim lastRow As Long
    Dim rowCount As Long
    Dim startCol As Long
    Dim ws As Worksheet
   
    'What is the starting column?
    startCol = Range("EI6").Column
    'What sheet do we work on?
    Set ws = ActiveSheet
   
   
    Application.ScreenUpdating = False
   
    With ws
        lastRow = .Cells(.Rows.Count, "BT").End(xlUp).Row
       
        For rowCount = 6 To lastRow
            Set myRow = .Range(.Cells(rowCount, "BT"), .Cells(rowCount, "EH"))
            'Check for numbers
            Set rngNum = Nothing
            On Error Resume Next
            Set rngNum = myRow.SpecialCells(xlCellTypeConstants, xlNumbers)
            On Error GoTo 0
           
            If Not rngNum Is Nothing Then
                colCount = startCol
                For Each r In rngNum.Areas
                    .Cells(rowCount, colCount).Value = r.Cells.Count
                    colCount = colCount + 1
                Next r
            End If
        Next rowCount
    End With
   
    Application.ScreenUpdating = True
   
End Sub