Add rows without interfering with VBA marcro's

[Stiff]

In VB I work with cells B10 to N50. In the cells above that, there is room for notes. The problem is that sometimes there are too less of rows to write down all notes. So you have to add a few rows. Whenever I do this, all of the VB code breaks, since all the cells get a different cellname (B10 becomes B11, B11 becomes B12 etc).

Anyone has a solution for this problem? I looked around the net, couldn't find anything usefull.

[Palmetto]

Post your code so we can look at it and provide a solution.

Create a named range for B10:N50 and use this named range in the VBA code. The range references will update as you insert cells and your VBA code should continue to work.

[Stiff]

Here is the code. Thought it wen't on till N50 but it goes on till N73.

Please Login or Register  to view this content.

[Palmetto]

Have you tried my earlier suggestion about creating a named range?
Create the named range and in each place in the code where you reference
range("B10:N73") used range("MyNamedRange").

where "MyNamedRange" is the real name you gave when you created the named range.

[Stiff]

I tried that but it doesn't work with all values..

Isn't there a way to write notes in one particular cell (like it's a word sheet) or something?

[Palmetto]

I tried that but it doesn't work with all values..

You must not have applied it correctly or have an invalid named ranged

See attached which uses this code and contains the named range "MyRange".
Click the button and it will insert a row at the active cell and then select the named range which will have shift from its original row reference when it was created.

MyRange originally refers to: =Sheet1!$A$10:$N$30

Please Login or Register  to view this content.

[Stiff]

Oh yes seem to work fine now. Though it's a lot of work to name every cell. Specially because this isn't the only worksheet I have to apply this.

A collegae of mine told me that it would be easier to force line break in the above cells. I know this can be done with Alt + Enter. But other people have to use this file too, so I was wondering if VBA can force certain cells to do a line break when pressing enter?