With Range .... why not working?

[franklin_m]

I'm fairly new working with Range variables and various actions. FYI, FindRows and FindCols are private functions that return number of non-empty rows as long and number of non-empty columns as long.

Not sure why this isn't working:

Dim NumberOfRows As Long
Dim NumberOfColumns As Long
NumberOfRows = FindRows("Report", 2, 1)
NumberOfColumns = FindCols("Report", 1, 2)
        
Dim FormatRng1 As Range
Set FormatRng1 = Range(Cells(1, NumberOfColumns), Cells(NumberOfRows, NumberOfColumns))

With Sheets("Report").Range(FormatRng1).Borders(xlEdgeLeft)
   .LineStyle = xlContinuous
   .ColorIndex = xlAutomatic
   .TintAndShade = 0
   .Weight = xlThin
End With

[mrice]

Try

Dim NumberOfRows As Long
Dim NumberOfColumns As Long
NumberOfRows = FindRows("Report", 2, 1)
NumberOfColumns = FindCols("Report", 1, 2)
        
Dim FormatRng1 As Range
Set FormatRng1 = Sheets("Report").Range(Cells(1, NumberOfColumns), Cells(NumberOfRows, NumberOfColumns))

With FormatRng1.Borders(xlEdgeLeft)
   .LineStyle = xlContinuous
   .ColorIndex = xlAutomatic
   .TintAndShade = 0
   .Weight = xlThin
End With

[franklin_m]

Thanks. Tried it and got a Run-time error "1004": Application-defined or object-defined error

[mrice]

On which line did the error occur?

[franklin_m]

This one:

Set FormatRng1 = Sheets("Report").Range(Cells(1, NumberOfColumns), Cells(NumberOfRows, NumberOfColumns))

[mrice]

Ok - try

Set FormatRng1 = Sheets("Report").Range(Sheets("Report").Cells(1, NumberOfColumns), Sheets("Report").Cells(NumberOfRows, NumberOfColumns))

[franklin_m]

Yep... that worked. Not sure what the proper terminology is, but it seems the "cells" has to be fully specified.

[mrice]

It depends which sheet is active when the line of code runs. The default is the active sheet. From what you reported, it looks like an sheet other than the Report sheet was active at the time.

[bakerman2]

To streamline a little.

With Sheets("Report")
    Set FormatRng1 = .Range(.Cells(1, NumberOfColumns), .Cells(NumberOfRows, NumberOfColumns))
End With

[xlnitwit]

Or perhaps

Set FormatRng1 = Sheets("Report").Cells(1, NumberOfColumns).Resize(NumberOfRows)