CODEWITHSUNDEEP
vbaexcel
jt2
jt2

Reputation: 61

Problems with Range

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

Upvotes: 3

Views: 156

Answers (3)

Davesexcel
Davesexcel

Reputation: 6984

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

Upvotes: 0

jt2
jt2

Reputation: 61

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.

Upvotes: 3

Maki
Maki

Reputation: 637

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

Upvotes: 0

Related Questions