Excelling

December 14, 2008

Relative Cell Referencing with Offset

Filed under: VBA — fatrunner @ 5:22 pm

There are times when you are recording a macro or writing code that you need to make a reference to a cell and that reference is not absolute, meaning that it’s position is relative to another cell whose position can change.   One example of this I can think of is if you are importing data into a spreadsheet and simply want to total it.  If the number of items in the list never changes the relative reference would not be necessary, but if the number of items can change, then you are better off using the relative reference instead.  In the first illustration if you knew that your total would always be in cell A9, then you could just code the sum formula in cell A9.  But in the second illustration the total is now needed to be in cell A10, and a relative reference is needed.

Sum Formula Resides in Cell A9

Sum Formula Resides in Cell A9

Sum Formula Needs to be Relative to the Last Number in the List

Sum Formula Needs to be Relative to the Last Number in the List

If you are simply recording a macro when you type in the sum formula then the macro recorder will  simply tell Excel to put the sum formula in cell A9 always.  If  you want it to always be at the bottom of the list of numbers then you will need to change the way the macro records (by changing the macro recorder to relative reference) or the way you write the code.  In order to make the code ‘know’ to go to the bottom of the list you will need to use the following code:

Range("A1").Select
    Selection.End(xlDown).Offset(1,0)).Select

For either illustration this formula would get you to the cell where you will need to type in the sum formula. I am stopping here for the simple fact that I am still learning all of this and I don’t know how to code the sum formula yet with a relative reference built into it. I still have to figure that part out and will post it in my next update.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: