PDA

View Full Version : Debugging Offset within a Range?



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

Kenneth Hobs
09-11-2019, 05:01 PM
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.


'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:

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

Artik
09-11-2019, 08:16 PM
Other way
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

Kenneth Hobs
09-11-2019, 08:54 PM
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

AmL2123
09-12-2019, 04:49 AM
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! :)

AmL2123
09-12-2019, 04:52 AM
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 :)