Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Assigning Range - Relative reference Vs Worksheet reference

    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ignore for now - I'm being a dumbass
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Colin_L View Post
    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)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Coolio.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •