Debugging Offset within a Range?

[AmL2123]

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.

Table Example.jpg


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?

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