Setting range with variable (1 Viewer)

tmyers

Well-known member
Local time
Today, 07:27
Joined
Sep 8, 2020
Messages
1,090
I am trying to set a range of cells using a variable and am getting a runtime error. I may be a different issue, but I believe I may not be using the variable correctly.

Code:
Option Explicit

Public Sub Reformat()

Dim LastRow     As Long
Dim wb          As Workbook
Dim ws          As Worksheet
Dim r           As Range
Dim c           As Range



    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Bid List")
    
    
        LastRow = Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
        
        Set r = ws.Range("A12:D" & LastRow)
        
        If LastRow = 11 Then
            
            Exit Sub
        
        Else
            With ws.Range(r)
                .Font.Size = 12
                .Font.Color = vbBlack
                .HorizontalAlignment = xlCenter
                .BorderAround = xlContinuous
            End With

        End If
    

End Sub
All I am trying to do is reformat a range of cells.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:27
Joined
Sep 21, 2011
Messages
14,051
And the runtime error is? :(

Walk through the code line by line with F8 and inspect what LastRow holds.
 

tmyers

Well-known member
Local time
Today, 07:27
Joined
Sep 8, 2020
Messages
1,090
Sorry Gas, typed this in a hurry.
Run-time error 1004: Metho range of object worksheet failed. Happens on the line
Wiki:
With ws.range(r)
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:27
Joined
Sep 21, 2011
Messages
14,051
OK, r is already a Range?

so just use
With r
 

Isaac

Lifelong Learner
Local time
Today, 04:27
Joined
Mar 14, 2017
Messages
8,738
and, FYI, the thing inside that parenthesis would have worked if it were a valid string representing a range address.
or, you can use 2 range objects for start and end of a multi cell range.

ws.range("a1")
ws.range("a1:b3")
ws.range(r,c) 'where r and c are range objects
 

Users who are viewing this thread

Top Bottom