Range error setting range in VBA using Range(Cells,Cells)

Question

I am reworking the code in an older worksheet to update it and make it more efficient.

I have a subroutine that formats a data table but I am getting an error where I dont see why I am getting one. My VBA is a bit rusty but the following code should be working

Sub FormatPnLDataTable(tableRange As Range)
    Dim tempRange As Range, ws As Worksheet
    Dim lStartRow As Long, lEndRow As Long, lLastCol As Long

    Set ws = Sheets("PandLDataTable")
    Application.CutCopyMode = False
    lStartRow = tableRange.Row
    lEndRow = lStartRow + tableRange.Rows.Count
    lLastCol = tableRange.Columns.Count

    'format the whole table with border
    With tableRange
        'format borders
        'code removed for brevity
    End With
    'set range for the top row of table
    Set tempRange = ws.Range(Cells(lStartRow, 1), Cells(lStartRow, lLastCol))

The code is erring on this line

 Set tempRange = ws.Range(Cells(lStartRow, 1), Cells(lStartRow, lLastCol))

but the worksheet (ws) is valid, and the variables all have their proper values for this particular instance, lStartRow is 1, lEndRow is 15 and lLastCol is 35

Try fully qualifying the `.Cells` property, and see if that helps. It would look like: `Set tempRange = ws.Range(ws.Cells(lStartRow, 1), ws.Cells(lStartRow, lLastCol))` — basodre, Jun 07 '16 at 17:25
True, if the active sheet is different than ws it would error out — n8., Jun 07 '16 at 17:27
See also: [Is the . in .Range necessary when defined by .Cells?](http://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells). — , Jun 07 '16 at 17:30
adding . to the Cells call is not appropriate here, and thus errs — dinotom, Jun 07 '16 at 17:44
The marked as duplicate is not even relevant as im not counting rows, which is already done correctly — dinotom, Jun 07 '16 at 17:45
The marked as duplicate is **exactly** the same identical problem. Read the post. This has been asked hundreds of times already. — Mathieu Guindon, Jun 07 '16 at 17:47
@Matt's Mug so when searching SO for this problem before I posted, I should have also looked for posts titled "Counting rows in another sheet"?..hardly — dinotom, Jun 07 '16 at 17:53
@dinotom - "marked as duplicate" is not a criticism of you personally and you shouldn't see it as such. It's just part of the process here to try to reduce the amount of duplication between posts. — Tim Williams, Jun 07 '16 at 18:18
@Tim Williams...I'm fully aware of that but trying to intimate that finding the post he is referring to with the problem I was having is absurd, I'd never find that post. — dinotom, Jun 07 '16 at 21:05

score 4 · Accepted Answer · answered Jun 07 '16 at 17:27

4

Set tempRange = ws.Range(Cells(lStartRow, 1), Cells(lStartRow, lLastCol))

Here although you've used ws to qualify the Range call, it does not automatically apply to the Cells calls - in a regular module these will by default refer to the ActiveSheet.

This will be more robust:

Set tempRange = ws.Range(ws.Cells(lStartRow, 1), ws.Cells(lStartRow, lLastCol))

See also: What is the default scope of worksheets and cells and range?

answered Jun 07 '16 at 17:27

Tim Williams

154,628
8
97
125

Yes, I normally wrap all the code for a sheet within a With statement and use .Cells, should've stuck with that format here as well. – dinotom Jun 07 '16 at 21:06

Range error setting range in VBA using Range(Cells,Cells)

1 Answers1