Debugging Offset within a Range?

tl;dr Does excel have issues with using offset within a range within an if statement?

I am trying to extract the, say, "B1000-1" from the header "Production Line : B10000-1" and populate the "Vessel No." column with that "B1000-1". I want to do this for each of the tables.

Attachment 25022


Longer Exposition:

I need to extract data from a table header and use that data to populate a column within the table. However, the length of the table varies from week to week when the sheet is updated, and the starting point/cell of the table varies since it’s also surrounded by other tables of varying length.

I am using a key phrase in the header to identify which cell it’s in, then identify a cell (which would be the top of the column I am trying to populate) by using a specific offset from the header. With that starting point, I want to FillDown the table length (which, to reiterate, is not constant from week to week).

Right now, I keep getting error messages with the line of code in red, which is attempting to determine the length of the table using the blanks between tables as an endpoint.

I’ve tested other ranges using (“D”&(i+1)), and those seem to work, but I can’t use that method since I don’t always know which cell in the D column the table will start on. Am I not allowed to use Offset within a range? How do I debug this?

Code:

---

With Worksheets(1).Range("A:A")
    Set c = .Find("Production Line", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            VesselNo = Right(c.Value, Len(c.Value) - 18)
            c.Offset(2, 4).Value = VesselNo

            Application.ScreenUpdating = False
            For i = 1 To 50
                If (Worksheets(1).Range("c.Offset(2, 3):c.Offset(2+i,3)") = "") Then
                j = i
                    'MsgBox j
                Exit For
              End If
            Next
            Application.ScreenUpdating = True

            Range("c.Offset(2, 4).Value:c.Offset(2+j,4)").FillDown
            Set c = .FindNext(c)
            Exit Do
        Loop While Not c Is Nothing
    End If

End With

---

Welcome to the forum!

If I were doing that, I would set a range to find for the usedrange in A. Then, find the first partial match. If found, search from there to end of usedrange for ending text of partial word "Total". You will then have ranges to offset for the fill. Put that in a loop and done. If you want an example, post back.

Code:

---

'If (Worksheets(1).Range("c.Offset(2, 3):c.Offset(2+i,3)") = "") Then
If Range(c.Offset(2, 3),c.Offset(2+i,3)) = "") Then

---

That will error too but the range is right. If you want to see if all cells in a range are blank:

Code:

---

Sub Main()    Dim r As Range
    Set r = [A1:B2]
    'If r = "" Then MsgBox "Blank"
    MsgBox Application.CountA(r) = 0, , "Blank"
End Sub

---

Other way

Code:

---

Sub BBB()
    Dim rngFind    As Range
    Dim l1stRow    As Long
    Dim strSearch  As String
    Dim lRow        As Long
    Dim VesselNo    As String

    strSearch = "Production line : "

    With Worksheets(1).Range("A:A")
        'Find the first occurrence of strSearch
        Set rngFind = .Find(strSearch, LookIn:=xlValues)
       
        'If found
        If Not rngFind Is Nothing Then
            'Remember row of first occurrence
            l1stRow = rngFind.Row
           
            Do
                'Please read Help about Split function.
                'We take the second element from the resulting array
                VesselNo = Split(rngFind.Value, strSearch, Compare:=vbTextCompare)(1)
               
                'Move one row down and three columns to the right,
                'and then "press End key and the Down Arrow key".
                'Read the row number and subtract one
                lRow = rngFind.Offset(1, 3).End(xlDown).Row - 1


                'Move two row down and four columns to the right,
                'and increase the range down by the calculated number of cells.
                'Insert VesselNo in this range
                rngFind.Offset(2, 4).Resize(lRow - rngFind.Row - 1).Value = VesselNo
               
                'Search for the next occurrence of strSearch
                Set rngFind = .FindNext(rngFind)
               
            'loop for as long as the cell row number found is greater than l1stRow
            Loop While rngFind.Row > l1stRow
           
        End If

    End With

End Sub

---

Artik

Code:

---

Sub ProdFill()   
    Dim ws As Worksheet, a As Range, f1 As Range, f2 As Range, v
   
    Set ws = ActiveSheet
     
    With ws
        'Force find from A1 and down.
        Set a = Union(.[A1], Intersect(.UsedRange, .Columns(1)))
        On Error Resume Next
        Do
            Set f1 = a.Find("Production Line", LookIn:=xlValues)
            Set f2 = a.Find("Total ", LookIn:=xlValues)
            v = Right(f1, Len(f1) - 18)
            Range(f1.Offset(1, 4).Address, f2.Offset(-1, 4)) = v
            Set a = Range(f2, .Cells(Rows.Count, "A").End(xlUp))
        Loop Until f1 Is Nothing
    End With
End Sub

---

Thanks for your warm welcome!

That's a really good idea to try that method of using the other key word to find the end of the column! I saw your code below as well, and I'll give it a try and dig through it to make sure that I understand what's happening in it. Thanks again! :)

And thank you so much as well, Artik! I'm also going to go through that code to understand it so that hopefully I can apply things in it in future things that I code :)