AmL2123
09-11-2019, 11:34 AM
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.
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?
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
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.
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?
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