CODEWITHSUNDEEP
excelvba
Eck
Eck

Reputation: 15

VBA - Find first cell with a border

I have a set of workbooks where I need to find the cell with the value "LAB #". After I find "LAB #" cell, I need to find the first cell after this with a bottom border. Border cell will always be in the same row and will always come after "LAB #" cell, but may be spaced differently in different workbooks. "LAB #" cell will always be in a different place in different workbooks.

Examples:

WS1: LAB # = P100, Border = S100

WS2: LAB # = AC12, Border = AG12

WS3: LAB # = M111, Border = Q111

etc!

I cannot change the workbooks themselves to be more uniform (I wish!).

I already have code to find and reference the "LAB #" cell, but finding the correct border cell has been difficult.

My thought is to create some kind of loop that goes through every cell in the "LAB #" row and tests each one for a bottom border; if it finds the border, I need the loop to set the variable (BorderCell = cell we just found) and end the loop. Otherwise we need to go to the next cell.

My VBA knowledge is very informal and what I've picked up over the years, so I'm struggling on this.

Code I use for finding "LAB #" cell (for the overall purpose, I have to use ActiveSheet, it's part of the larger goal and works fine in this regard):

Dim LabCell As Range
Dim BorderCell As Range

With ActiveSheet
    Set LabCell = .Cells.Find(what:="LAB #", LookIn:=xlValues, LookAt:=xlWhole)
    If LabCell Is Nothing Then
        MsgBox "'LAB #' not found"
    Else
        'this is where I need code for finding border cell and declaring it
    End If
End With

Upvotes: 0

Views: 669

Answers (1)

FunThomas
FunThomas

Reputation: 29181

The following function will return the first cell below a specific cell with a bottom border. It stops after a certain number of cells (and return nothing) if no cell is found.

Function FindCellWithBorder(startCell As Range, Optional MaxRows As Long = 100) As Range
    Dim offset As Long
    For offset = 1 To MaxRows
        If startCell.offset(offset, 0).Borders(xlEdgeBottom).LineStyle <> xlNone Then
            Set FindCellWithBorder = startCell.offset(offset, 0)
            Exit Function
        End If
    Next
End Function

Call it like this:

If LabCell Is Nothing Then
    MsgBox "'LAB #' not found"
Else
    Dim borderCell as range
    Set borderCell = FindCellWithBorder(LabCell)
    If borderCell Is Nothing Then
         MsgBox "No cell with border found..."
    Else
       (...)
    End If
End If

Just be aware that if the cell you are looking for has also a Top border, the function will return the cell above that cell as the Top border of your cell is also the bottom border of the cell above.

Upvotes: 1

Related Questions