The UK's Number 1 for Microsoft Office Training

Sitemap

Bookmark page

Printable version

Course updates
due to adverse
weather conditions

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » FormulaR1C1 notation help

	98.3 % recommend us
	More live stats...

Bookmark this page

Share this with a friend or colleague:

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

FormulaR1C1 notation help

Resolved · High Priority · Version 2007

ReplyWed 3 Dec 2008, 10:28Delegate Andrew said...

FormulaR1C1 notation help

Hi all,

I was wondering how in VBA where we have the FormulaR1C1 notation as:

activecell.FormulaR1C1 = "=SUM(RC[+1]:RC[+3])"

If how many rows/columns back I want to go is variable, how can I set the +1 and +3 values to some variable integer?

For upcoming training course dates see: Pricing & availability

ReplyWed 21 Jan 2009, 13:49 Edited on Wed 21 Jan 2009, 13:50Trainer Laura said...

RE: FormulaR1C1 notation help

Hi Andrew

Thank you for your question regarding using FormuaR1C1.

The FormulaR1C1 property is a string so to use variables for the column offset in the formula you would need to add the different pieces of the string together using &.

Using your example and two integer variables iColOffset1 and iColOffset2 the code would change to

     ActiveCell.FormulaR1C1 = "=SUM(RC[+" & iColOffset1 & "]:RC[+" & iColOffset2 & "])"

I hope that helps.

Please let me know how you get on.

Laura GB

Tue 27 Jan 2009: Automatically marked as resolved.

Excel articles

· Unsupported features in Excel 2007
· Break The Mystery Of Code With Excel Macros
· Correct In, Garbage Out
· Microsoft Excel provides the building blocks of shared information
· Understanding XML Within Excel

Please browse our web site to find out more about
exce+training and other Microsoft training courses.

Excel tip:

Highlighting a data range

Attempting to use a mouse to highlight a large range of cells with data in Excel can make the mouse to have a life of it's own!

Use keyboard strokes instead.

Step 1. Place the cusor in the cell where the highlighting should begin.

Step 2. Select keystroke, CTRL+SHIFT+END

This will take the cursor to the furthermost bottom corner of the data range found in that worksheet. And highlight that range of cells at the same time

View all Excel hints and tips

Distance Learning Vs Face To Face Tuition

Over the years distance learning has become a popular and convenient way of studying for GCSE's, A Levels, professional qualifications and even degrees thanks to the introduction of web-based settings, applications and tools. But, can distance learning provide the same kind of learning experience as a classroom environment?

» Article: Training providers

I have a form with a combo box to filter results in the inner form that i have generated from an sql statement but i do not thing the sql is correct it works but it comes up with a box asking for the data that i thought would come from the combo box, can ..