How to make a macro cell independent

**Ads.** · 10-17-2011, 07:11 AM

Hi,

I am new to macro recording and VBA. How do I make a macro cell independent? For example, I currently have:

Range("K4").Value = Range("K4").Value - 1

i.e. minus 1 from the current value in the cell K4.

Say then I move the content of K4 to some new location, e.g. I've added a new row above so K4 has now been shifted to K5. How do I program it so that the macro automatically performs the original operation in the new arbitary location?

Thanks for any help

**quekbc** · 10-17-2011, 07:13 AM

Hi Ads,

When it comes to referencing a specific cells in VBA, it is recommended that we use named ranges instead. That way, adding in rows/columns cut/paste will not affect (to a certain degree) the VBA code.

**royUK** · 10-17-2011, 07:59 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

ADDED THIS TIME

**royUK** · 10-17-2011, 08:00 AM

is there data below thye relevant cell?

**Ads.** · 10-17-2011, 08:18 AM

Yes. The macro is being applied to a cell in a table. So I need the macro to work if either a new row is added to the table, or the table is to moved to a different position in the spreadsheet (much like if you have a formula in a cell, the formula will still work identically if the cell is moved around)

**romperstomper** · 10-17-2011, 08:21 AM

Then you need a named range, as suggested, or some means of identifying the cell in question so your code can find it.

**Ads.** · 10-17-2011, 08:29 AM

Ok thanks. I will attempt that.

**Ads.** · 10-17-2011, 08:38 AM

Marvellous. Just what I was after. Thanks quekbc and romperstomper.

**romperstomper** · 10-17-2011, 08:43 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save