vba - VBA Find Multiple Headers and Set Range as Multiple Columns

Question

I'm new to coding VBA. I'd like to do the following: 1. Find multiple specific headers in a table. 2. Set the columns of these headers as a variable. 3. Change the font size of the variable to 14.

The location of the specific headers in the table often changes, so I'm using the find method. To start off, I'm just seeing if I can successfully change the font size of the specific headers. But alas, I'm getting an error on the last line: "Object variable or With block variable not set".

Sub ASOLDPrintFormat1()

Dim ReferenceCell As Range
Dim WS As Worksheet
Dim Table As Range
Dim BigColumns As Range

Set ReferenceCell = ActiveCell
Set WS = Sheets(2)

 With WS
    Set ReferenceCell = Cells.Find(What:="Source #", LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

Set Table = ReferenceCell.CurrentRegion

With Table
    Set BigColumns = Cells.Find(What:="Source Well:Sample _ 
    ID:VerboseConc_uM:VerboseConc_ug/ml:Mol. Wt:N/Mole", _
    LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = True
    .ReadingOrder = xlContext
    .MergeCells = False
End With     

With BigColumns
    .Cells.Font.Size = 14
End With    
End With

End Sub

I'm getting an error on the last line: "Object variable or With block variable not set".

标签： vba

Answer 1

You need to account for the chance your value is not found when using Range.Find method. When your search value is not found your variable ReferenceCell = Nothing. Trying to find the CurrentRegion of Nothing or changing the font of Nothing will produce your error. The same is true when you are setting BigColumns.

The below example shows you how to resolve for the first instance of error - replicate this anytime you are using Range.Find (unless you are 100% sure the value you are searching for exists)

With WS
    Set ReferenceCell = .Cells.Find(What:="Source #")

    If Not ReferenceCell Is Nothing Then
        Set Table = ReferenceCell.CurrentRegion
    Else
        MsgBox "'Source #' Not Found"
        Exit Sub
    End If

    'Continue with code here.

    With Table
        '....
        '....
        '....

---

You are also not using your With block correctly. You need to qualify your objects related to the block with a period. (Cells does not refer to block, .Cells does)

---

If your value is not being found although it does indeed exist, you can check your Range.Find syntax for accuracy here as you may not be searching correctly.