Assigning Range - Relative reference Vs Worksheet reference

pootle flump · #1 (**permalink**) 10-29-09, 07:41

Hi

I have this in a Worksheet module:

Code:

Range(Cells(1, 2), Cells(12, 10)).Value = 1

Works fine.
Change it to:

Code:

Worksheets("tester").Range(Cells(1, 2), Cells(12, 10)).Value = 1

or

Code:

ActiveSheet.Range(Cells(1, 2), Cells(12, 10)).Value = 1

and it fails.

Any way I can use this syntax when referring to a specific sheet? I would prefer to avoid using "B1" notation since I am iterating using integers however I'm open to other suggestions

Thanks
poots

pootle flump · #2 (**permalink**) 10-29-09, 07:53

Ignore for now - I'm being a dumbass

Colin Legg · #3 (**permalink**) 10-29-09, 08:30

Just in case someone else researching this issue happens to read this thread....

Qualify the cells properties in the same way as the range property:

Code:

ActiveSheet.Range(Cells(1, 2), Cells(12, 10)).Value = 1

---->

Code:

ActiveSheet.Range(ActiveSheet.Cells(1, 2), ActiveSheet.Cells(12, 10)).Value = 1

pootle flump · #4 (**permalink**) 10-29-09, 08:34

Ok - what I was looking for was:

Code:

Worksheets("tester").Range(Worksheets("tester").Cells(1, 2), Worksheets("tester").Cells(12, 10)).Value = 1

However, now I've solved that simplification of my problem I am stuck again.

So, adding stuff in slowly that want to use:

This works

Code:

Worksheets("Tester").Range(Worksheets("Tester").Cells(1, 1), Worksheets("Tester").Range("A1").End(xlToRight)).Value = 1

However this fails:

Code:

Worksheets("Tester").Range(Worksheets("Tester").Cells(1, 1), Worksheets("Tester").Range(Worksheets("Tester").Cells(1, 1)).End(xlDown)).Value = 1

This works:

Code:

Worksheets("Tester").Range("A1").End(xlDown).Value = 1

This fails:

Code:

Worksheets("Tester").Range(Worksheets("Tester").Cells(1, 1)).End(xlDown).Value = 1

Is the short of this that I can't assign a range property using .Cells if I want to use the .End method?

pootle flump · #5 (**permalink**) 10-29-09, 08:35

Quote:

Originally Posted by Colin_L

Qualify the cells properties in the same way as the range property:

Yup - that was the point I realised I was being a dumbass.

Just relieved I didn't ask my follow up question (where the solution was not to confuse xlRight and xlToRight)

Colin Legg · #6 (**permalink**) 10-29-09, 08:43

Does this work for you?

Code:

Sub foo()

    Worksheets("Tester").Range( _
        Worksheets("Tester").Cells(1, 1), _
        Worksheets("Tester").Cells(1, 1).End(xlDown) _
                                ).Value = 1


End Sub

pootle flump · #7 (**permalink**) 10-29-09, 08:48

Ah crap - of course

I was editing some existing code. Obviously, the use of .Range there was because it was referring to the cell as "A1". I was just nesting statements without realising I didn't need to.

Awesome - thanks Colin - I've been battling with that for ages.

Colin Legg · #8 (**permalink**) 10-29-09, 08:53

Coolio.

I know exactly what you mean, nested Ranges and Cells can very quickly get confusing, especially when you're editing existing code.

Internet Services

Web Development

Digital Marketing

Consumer Tech