Problems with Range

Question

Why doesn't the second format of Range(Cells(),Cells()) usage work?

Sub start()

    Dim ws As Worksheet
    Dim wb As Workbook
    Dim cond_rng As Range

    Set ws = Application.Workbooks("ndata.xlsm").Worksheet("conditionsSheet")

    ' This works and selects one cell
    Set cond_rng = ws.Cells(4, 1)
    cond_rng.Select

    'this works as expected
    Set cond_rng = ws.Range("A1:B10")
    cond_rng.Select

    'this fails with error 1004
    Set cond_rng = ws.Range(Cells(1, 1), Cells(4, 4))
    cond_rng.Select

I do not get an error when i run this. selects range A1-D4 – Calum Mar 18 '15 at 01:00 — Calum, Mar 18 '15 at 01:00

score 3 · Answer 1 · edited Mar 18 '15 at 01:07

3

Ok, well this works:

'this works
Set cond_rng = ws.Range(ws.Cells(1, 1), ws.Cells(4, 4))
cond_rng.Select

or

With Sheets("conditionsSheet")
    .Range(.Cells(1, 1), .Cells(4, 4)).Select
End With

The .Cells is important, as it won't work with just Cells.

edited Mar 18 '15 at 01:07

L42

19,427
11
44
68

answered Mar 18 '15 at 01:02

jt2

61
3

1

Writing it the original way makes it dependent on the active sheet. It's equivalent to `Set cond_rng = ws.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(4, 4))` If "conditionsSheet" is active then things will be fine whereas if any other sheet is active you will get the error. The two ways shown in your answer remove the dependency on the active sheet – barrowc Mar 18 '15 at 01:04
1

Up one. You nailed it. You need to explicitly reference your objects. [See this post to know more how you'll directly work with objects](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). You might want to add @barrowc explanation in your post to improve context. – L42 Mar 18 '15 at 01:05

score 0 · Answer 2 · answered Mar 18 '15 at 01:00

0

I tested with a simple sub like the below and it works. I think your error is coming from missing "s" after "worksheet".

Set ws = Application.Workbooks("ndata.xlsm").Worksheet("conditionsSheet") should be ...Worksheets....

sub TEST()

Dim ws As Worksheet
Dim cond_rng As Range

Set ws = Worksheets("sheet1")
Set cond_rng = ws.Range(Cells(1, 1), Cells(5, 4))
cond_rng.Value = 5

End Sub

answered Mar 18 '15 at 01:00

Maki

625
4
11
26

I think your test works as you only have one worksheet, and it is the active one. The sheet I was trying to refer to was inactive, and without the ws.Cells reference, it didn't know which object to use. Thanks for the answer though....funny that VBA didn't complain about Worksheet vs. Worksheets... – jt2 Mar 18 '15 at 01:04
you should active it, and if you want to keep the process in the background just use screenupdating = false – Maki Mar 18 '15 at 01:06
I'm trying to avoid having to activate all these different worksheets, and I just went through a heck of a time trying to figure out why Range("A1:B10") worked but Cells(1, 1), Cells(4, 4)) didn't. I finally saw a web page showing how to use ranges on inactive worksheets, and saw that the Cells needed an object qualifier too.... sheesh! – jt2 Mar 18 '15 at 01:09
well, it seems like you've got it now. [thumbs up] – Maki Mar 18 '15 at 01:12

Davesexcel · Answer 3 · 2015-03-18T08:33:40.633

I believe it is the range.select that is confusing people. you will usually get an error if you are trying to select a range from a non-active worksheet.

If you are trying to get values from that range in another sheet or need to add info into that range, you don't have to select it. For example: the below code will loop through each sheet and copy A1:D4(excluding PasteSheet) and paste it into PasteSheet. For this example make sure you have a sheet named, "PasteSheet"

Sub SheetLoop()
    Dim ws As Worksheet
    Dim sh As Worksheet

    Set ws = Sheets("PasteSheet")

    For Each sh In Sheets
        If sh.Name <> ws.Name Then
            With sh
                .Range(.Cells(1, 1), .Cells(4, 4)).Copy ws.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            End With
        End If
    Next sh
End Sub

You can run this code from any sheet in the workbook because you don't have to select anything.

So if for example Sheet2 A1=2 then sheet1(A1:D4) color is red else it is Blue.

Sub Button2_Click()
    Dim ws As Worksheet, x
    Dim sh As Worksheet, rng As Range, Crng As Range
    Set ws = Sheets("Sheet1")
    Set sh = Sheets("Sheet2")
    Set rng = sh.Range("A1")
    Set Crng = ws.Range(ws.Cells(1, 1), ws.Cells(4, 4))

    x = IIf(rng = 2, vbRed, vbBlue)
    Crng.Interior.Color = x
End Sub

Problems with Range

3 Answers3