Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Excel R1C1 Notation


Reply
 
Thread Tools Display Modes
  #1  
Old 10-10-2003, 10:56 AM
paulmcneil paulmcneil is offline
Newcomer
 
Join Date: Oct 2003
Posts: 1
Question Excel R1C1 Notation


Anyone have an example of selecting a range in Excel VBA using R1C1 notation?
Reply With Quote
  #2  
Old 10-10-2003, 02:45 PM
Mill Mill is offline
Just another Excel nerd

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,631
Default

Quote:
Originally Posted by paulmcneil
Anyone have an example of selecting a range in Excel VBA using R1C1 notation?



Range("D1:D5").Select?
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
Reply With Quote
  #3  
Old 10-10-2003, 05:11 PM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

Why would you make use of the R1C1 notation ?
It's so hard to read/edit....
Reply With Quote
  #4  
Old 10-10-2003, 10:27 PM
MeHere MeHere is offline
Freshman
 
Join Date: Jun 2003
Posts: 30
Default

Have you tried the Cells property which is another way to reference a range? If you like the column numbers instead of letters then you'll love the Cells property.

Range("D1:E5").Select can be done with the Cells property like this:

Range(Cells(1,4),Cells(5,5)).Select

The syntax is: object.Cells(rowIndex, columnIndex)
Reply With Quote
  #5  
Old 10-11-2003, 02:15 AM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

Don't forget the R1C1 notation goes like this...
"=SUM(R[-7]C:R[-1]C)"
To be honest i even wouldn't know how to select a range this way.
Never done that and i probably never will...
For those ho want to use the R1C1 notation :
Goto Tools > options > general and select use R1C1 reference style.
Reply With Quote
  #6  
Old 10-11-2003, 01:58 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

The Range() property (essentially is a function) cannot accept R1C1-style notation for the input string. It can take either strings of A1 notation, or Cell/Range inputs.

The following all *attempt* to Select the same Range:
Code:
Range("A1:E10").Select ' Works fine. Range("R1C1:R5C10").Select ' FAILS! Range(Cells(1, 1), Cells(5, 10)).Select ' Works fine.
For your purposes, it would seem that the Range(Cells(),Cells()) technique might be best?

-- Mike
Reply With Quote
  #7  
Old 10-12-2003, 01:12 AM
Dangleberry Dangleberry is offline
Contributor
 
Join Date: Sep 2003
Location: Australia
Posts: 413
Default

Quote:
Originally Posted by italkid
Don't forget the R1C1 notation goes like this...
"=SUM(R[-7]C:R[-1]C)"
To be honest i even wouldn't know how to select a range this way.
Never done that and i probably never will...
For those ho want to use the R1C1 notation :
Goto Tools > options > general and select use R1C1 reference style.



Italkid, Isn't this effectively what you're talking about for r[-7]c:r[-1]c

Code:
range(activecell.offset(-7,0),activecell.offset(-1,0)).select '''or if you prefer range(cells(activecell.row - 7,activecell.column), _ cells(activecell.row-1,activecell.column)).select
Reply With Quote
  #8  
Old 10-12-2003, 02:51 AM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

Indeed but thats the selection made through a formula end recorded with the macrorecorder.
Try to select a range using VBA that way...
Thus as Mike_R explained in his reply.
Remember the original question :
[quote]
Anyone have an example of selecting a range in Excel VBA using R1C1 notation?
[/vb]
Since Paulmcniel doesn't mention a formula i still assume he only asks how
to select a range using VBA and the R1C1 notation way.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Returning control (Focus) to Word from Excel new2vba Word, PowerPoint, Outlook, and Other Office Products 6 02-19-2009 09:30 AM
Can't get rid of save file prompt in Excel with XP version of Excel Groo Excel 6 09-08-2003 10:09 PM
VB6 and Excel wolrabs Excel 2 08-07-2003 03:26 PM
Excel crashing... Syko10-96 Word, PowerPoint, Outlook, and Other Office Products 12 11-17-2002 06:50 AM
How to call VB code from Excel? Smokefox66 General 6 07-26-2001 03:24 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->