Can I make my VBA code automatically update the cell location?

[h711]

Hi, if I write VBA code to modify cell B3. Later on I inserted a new row above it, so now B3 became B4. The problem is my VBA code will still modify cell B3 every time I run it, not the new location B4.

Is there a way to make VBA code automatically update the cell location? Just like if you use = assignment in a cell, the referred cell will be automatically updated by excel every time you insert or delete rows/columns.


Thanks for your help.

Jianhua Huang

 

[VoG]

You could make the cell a named range (Insert > Name > Define) then refer to it as the range name e.g. Range("MyRange")

 

[h711]

Thanks for the replay. could you give me a simple example for how to do this?

For example, here is a simple macro I recorded to insert abc to cell B2:

Sub Macro1()

Range ("B2").Select
ActiveCell.FormulaR1C1 = "abc"
Range ("B3").Select

End Sub

Now how can I modify this code to auto update this "B2" to a new location if I insert/delete row/column that changes the cell location?

Thanks you very much for your help!!!!!

 

[VoG]

I named cell B2 as MyRange and ran this macro

Sub example()
Range("MyRange").Value = "abc"
End Sub

which placed abc in B2. I then deleted the contents of B2, inserted 3 rows above it then ran the macro again. abc was written into B5 (which now has the name MyRange).

 

[h711]

It works. Thanks a lot!!!