-
Nov 10th, 2014, 02:34 PM
#1
Thread Starter
Hyperactive Member
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.
-
Nov 10th, 2014, 03:06 PM
#2
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
-
Nov 10th, 2014, 04:10 PM
#3
Re: A strange error regarding multiple ranges
Originally Posted by vbfbryce
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.
Originally Posted by Daniel Duta
... 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?
-
Nov 10th, 2014, 07:57 PM
#4
Re: A strange error regarding multiple ranges
I did not know that! Thanks for enlightening me, but please do not call my "Byrce!"
-
Nov 10th, 2014, 08:09 PM
#5
Re: A strange error regarding multiple ranges
Ok, Shirley, you have made a typo before.
-
Nov 10th, 2014, 08:38 PM
#6
Re: A strange error regarding multiple ranges
Love the Airplane refs...why is my font so lalarge on my phone?!
-
Nov 10th, 2014, 08:39 PM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|