What is an alternative to Union() when referring to multiple cells?

**Dante_Blake** · 05-07-2012, 12:44 PM

I have the following line from a plotting script I made:

Please Login or Register  to view this content.

It works fine in Excel 2010, but fails in Excel 2003.

The variable y is dimensioned as an integer and is either 0 or 1. When it gets to the two lines highlighted in red I get a Run-time error '1004': Method 'Cells' of object '_Global' failed. y is referenced many times prior to that line of code without issue, so I just appears to be a problem with how I attempt to select two different cells on the worksheet.

Thanks!

**lloydgodin** · 05-07-2012, 01:17 PM

Can you upload your worksheet as a reference?

**Dante_Blake** · 05-08-2012, 08:27 AM

I can't, it's way too big for my available upload speed.

I know you can select multiple ranges like:

Please Login or Register  to view this content.

However, if I remove teh quotes and replace the letter/number cell address, it will treat it as a start and end for one large range.

**JosephP** · 05-08-2012, 08:32 AM

you haven't said what the problem actually is?

**Dante_Blake** · 05-08-2012, 09:16 AM

I modified the original post to [hopefully] better state the issue.

**StevenM** · 05-08-2012, 09:20 AM

What is an alternative to Union() when referring to multiple cells?

A comma is the Union operator.

Please Login or Register  to view this content.

Thus:

Please Login or Register  to view this content.

For example, if y = 2:
Then Range("C" & 3 + 7 * y & ",C" & 5 + 7 * y).Address = "$C$17,$C$19"
And Union(Cells(3 + 7 * y, 3), Cells(5 + 7 * y, 3)).Address = "$C$17,$C$19"

**JosephP** · 05-08-2012, 09:21 AM

you need to refer to the sheet the cells are on. also better not to select stuff

Please Login or Register  to view this content.

note the periods before the Cells calls to refer them back to the worksheet object referred to by the With statement.

**Dante_Blake** · 05-08-2012, 01:05 PM

Neither of those worked for me. I ended up doing the following:

Please Login or Register  to view this content.

Which is just a recorded macro I hacked up to include the differing rows.

What is an alternative to Union() when referring to multiple cells?

LinkBack

Thread Tools

Rate This Thread

Display

What is an alternative to Union() when referring to multiple cells?

Re: What is an alternative to Union() when referring to multiple cells?

Re: What is an alternative to Union() when referring to multiple cells?

Re: What is an alternative to Union() when referring to multiple cells?

Re: What is an alternative to Union() when referring to multiple cells?

Re: What is an alternative to Union() when referring to multiple cells?

Re: What is an alternative to Union() when referring to multiple cells?

Re: What is an alternative to Union() when referring to multiple cells?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions