VBA and cell references

[gimli]

Hey all,

Ok..just noticed that when your insert cells..rows etc it doesnt update VBA code references. So some of my code is fubared cause I was inserting new rows, columns etc. I know I know..im a fool.

Any way around this?

thanks much

[lucas]

dynamic named ranges for one.

again, how can we give you advice on such vague information?

[gimli]

Ok.. I attached a file with a sub. Not sure how to code it with dynamic named ranges. Right now if i insert new columns before column F , F should be come G in the vba code. Think im explaining that right

[lucas]

Couple of things about your attachment.

Code like that should be in a standard module, not in the sheet code module. You can qualify it easily enough. I changed your code and added Option explicit which you should always use in every module.

About your original question. Can you use formula's to do what you want done or are you just interested in whether what you want to do can be done?

It would be very difficult using VBA. I'm not going to say impossible because I've stepped in that pile before and been proven wrong.....

[gimli]

Thanks for the input.

I think im just not explaining myself very well. The code that you uploaded obviuosly works. I guess my question is...

When I insert a column on sheet 1 before column F... I want the code to follow the changes...just like if I had formulas entered in cells and I inserted new columns or rows.

Basically..I had a bunch of code referencing cells. I then added columns and rows which made the cell references in the code invalid. So I had to upodate the code with the new cell references. Any way to write code so that if the reference cell changes due to inserting new colums or rows it changes with it?

Think im explaining that right

[lucas]

again, I'm going to say no but it's possible that someone will prove me wrong.

[CDTech]

Basically, you need reference cells.

1) Create variables that hold row and column addresses of your reference cells then alter the variables as your code inserts. Starting position must be fixed each time you open the workbook.

2) If you are doing the insertions, create a large enough range to contain your reference cells then find the contents in your reference cells in said range. Your reference data cannot change unless you modify your code each session.

If you want VBA to modify it's code in the workbook, I can't help.

[gimli]

Ok..thanks a bunch for the input