Can cell references change automatically in code?

**LampCommandr** · 03-25-2011, 11:36 AM

Hey folks,

Pretty straight forward question - is there a way to maintain the references in VBA relational to changes made in the sheet?

I have added quite a bit of functionality and references in VBA to specific cells and each time I add a row I then have to update all of my code to shift that reference down as well.

Is there any way around this?

**StephenR** · 03-25-2011, 11:39 AM

Maybe use offset. Can you give a specific example of what you are doing?

**MarvinP** · 03-25-2011, 12:13 PM

Hi LampCommandr,

I have two suggestions. I use in almost every routine I write the following"

Please Login or Register  to view this content.

Which returns the last non-blank row in Column A. You can change the "A" to "D" if you need the last non blank in Col D. See:
http://www.rondebruin.nl/last.htm

A second way is to use a Dynamic Named Range of data and have your formulas refer to the Range by Name instead of cells.
See http://www.bettersolutions.com/excel...G820716330.htm

**royUK** · 03-25-2011, 12:23 PM

If you refer to a specific cell with something like

Please Login or Register  to view this content.

Then you need to change that if if is no longer relevant. As marvin says an example would help us make a better suggestion

**LampCommandr** · 03-25-2011, 01:15 PM

Sorry folks, here is an example:

Please Login or Register  to view this content.

What ends up happening with this code is when I add a row before/after/in-between the referenced Ranges/Rows in my code, I'll then need to edit my code to reflect this.

As an example, if I added a new row, let's say a row between Rows 1 and 2, I would then need to increase all the references in my code by 1.

Hope that clarifies things.

**MarvinP** · 03-25-2011, 02:44 PM

I believe that if you use Named Ranges in your formulas, they will move when you insert or delete rows.

hope this helps.

**royUK** · 03-25-2011, 03:12 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**LampCommandr** · 03-25-2011, 03:43 PM

Thanks for the responses.

Roy,

All I'm trying to figure out is if there is a way that I can reference cells in my code so that every time I add a row that causes references to shift I don't have to adjust all my code.

Using the example from earlier:

Before:

Please Login or Register  to view this content.

In this code I have made several references to specific cells and rows. If I were to add a row to the sheet at the very top I get the following error:

"Type mismatch"

Then I need to change my code to reflect the following:

After:

Please Login or Register  to view this content.

I have to go back through all my code and make changes to the references and increase them by one to accomodate the added row.

I'm wondering if there is a way to code to get around this manual process of having to adjust all references.

**LampCommandr** · 03-25-2011, 03:48 PM

Originally Posted by MarvinP

I believe that if you use Named Ranges in your formulas, they will move when you insert or delete rows.

hope this helps.

MarvinP,

This might do the trick. I could potentially use this for the specific cell references, but would it work for row references like in my example above?

Also, is there a way to reference a named table column (similar to just working with formulas) - such as Table_Data[Column Name]?