Consulting

Results 1 to 6 of 6

Thread: Debugging Offset within a Range?

  1. #1

    Exclamation 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.

    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
    Last edited by AmL2123; 09-11-2019 at 12:23 PM.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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

  3. #3
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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

  5. #5
    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!

  6. #6
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •