Results 1 to 7 of 7

Thread: A strange error regarding multiple ranges

  1. #1

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    A strange error regarding multiple ranges

    Hello all,

    Last week I met a very curious behavior regarding a common method of write a bunch of ranges in a single line, such as objExcelApp.Range("A1, B2:B4, C2:C3").Value = 3. It doesn't matter the cells inside the ranges are contiguous or not, this method always worked to me. But these days I have noted that this method fails in some computers which have the same OS and Office. I have no explanation for this. Each time a 1004 run-time error occurs ("Select method of Range class failed"). But why this happens only with some computers considering that we have the same system (windows7, office 2010, the same upgrades etc.).
    To avoid this error I have to use Union method, which even it is longer it works. So instead of objExcelApp.Range("C2, C4, C6").Value = 77 which is very convenient in terms of length I have to write

    With objExcelApp
    .Union(.Range("C2"), .Range("C4"), .Range("C6")).Value = 77
    End with

    Would someone experienced something like this ? Thank you in advance.
    Last edited by Daniel Duta; Nov 10th, 2014 at 02:39 PM.

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: A strange error regarding multiple ranges

    The range object belongs to the worksheet, not to the application, so something more like this:

    Code:
    Sub rngUnion()
        Dim ws As Worksheet
        Dim rng As Range
        
        Set ws = ActiveSheet
        Set rng = ws.Range("a1,b2:b5,c9")
        rng.Value = 99
    End Sub

  3. #3
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: A strange error regarding multiple ranges

    Quote Originally Posted by vbfbryce View Post
    The range object belongs to the worksheet, not to the application, ...
    Sorry Byrce, but Range is a property on the Application object as well. It is a shorthand way of doing Application.ActiveSheet.Range. It could throw an error if the ActiveSheet is a a Chart.

    Quote Originally Posted by Daniel Duta View Post
    ... Each time a 1004 run-time error occurs ("Select method of Range class failed").
    This is bizarre error to have attributed to "objExcelApp.Range("C2, C4, C6").Value = 77" as there is no selection happening in that statement. Are you certain that it is that line that is causing the error? Are there any Range.Select statements immediately before or after that line of code?

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Cool Re: A strange error regarding multiple ranges

    I did not know that! Thanks for enlightening me, but please do not call my "Byrce!"


  5. #5
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: A strange error regarding multiple ranges

    Ok, Shirley, you have made a typo before.

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: A strange error regarding multiple ranges

    Love the Airplane refs...why is my font so lalarge on my phone?!

  7. #7
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: A strange error regarding multiple ranges

    Edit: nvm, it's only while I'm typing!

Posting Permissions

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



Click Here to Expand Forum to Full Width