Consulting

Results 1 to 8 of 8

Thread: Solved: Range object acting up in 2007

  1. #1
    VBAX Regular ntrauger's Avatar
    Joined
    May 2011
    Location
    Monticello
    Posts
    38
    Location

    Solved: Range object acting up in 2007

    Anyone know why this would fail in Excel 2007?[vba]Range(Cells(53,6), Cells(53,106)).Interior.Color = 0[/vba] I get "Method range of object worksheet failed".

    A much smaller column argument in the second Cells property will avoid the error, as will different syntax, but I'm curious what's going on here to throw an error. Doesn't look like it should.
    --Nate

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,902
    Location
    Works fine for me in 2010 on Vista64. It will error 1004 if any of those cells are locked and the sheet is protected.

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,784
    Neither range nor cells is qualified so you're depending on defaults, which in turn depends on where the code is situated and/or what the activesheet is at the time.
    For example, I wouls expect the code to fail if the code were in a userform's code module and a chart sheet was active at the time.
    So, predictablty, the questions arise:
    1.Where is the code?
    2.What kind of sheet is active at the time?
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Regular ntrauger's Avatar
    Joined
    May 2011
    Location
    Monticello
    Posts
    38
    Location
    Sounds like a good point. I have the code in a worksheet and nested in a with block something like:[vba]Set wsNew = Sheets.Add
    With wsNew
    .Range(Cells(53,6),Cells(53,106))
    End With[/vba]where the cells properties are refering to the current sheet and the Range is refering to the new sheet. Doesn't make sense why a smaller argument would not throw the error, but perhaps I went too quickly and missed something. I'll have to check it again later this evening.
    --Nate

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,718
    [vba]Sheets.Add.cells(53,6).resize(,100).interior.colorindex=6[/vba]

  6. #6
    To correct your own code, replace

    .Range(Cells(53,6),Cells(53,106))
    with

    .Range(.Cells(53,6),.Cells(53,106))
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,784
    picky picky:
    Quote Originally Posted by snb
    [vba]Sheets.Add.cells(53,6).resize(,101).interior.colorindex=6[/vba]
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Regular ntrauger's Avatar
    Joined
    May 2011
    Location
    Monticello
    Posts
    38
    Location
    Alright. Thanks guys.
    --Nate

Posting Permissions

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