absolute referencing

dtchang

New Member
Joined
Nov 19, 2011
Messages
24
i am wondering if its possible in vba code for references to stay the same even if i insert rows/columns in the workbook.

for example

Code:
Sub movingselect()

Dim hello As Range

Set hello = Range("$b$4")
hello.Select


End Sub

in cell b4 ive typed "david". if i insert a bunch of columns in column A moving the "david" to say column F, i would like the vba to follow that cell so the "hello" range still refers to whatever cell the text "david" is in.

how do i do this in vba? thanks in advance.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
The easiest way is to name the range
Code:
Range("$B$4").Name = "HelloCell"
To use it again
Code:
Range("HelloCell").Select
 

dtchang

New Member
Joined
Nov 19, 2011
Messages
24
thanks for the quick reply!

sorry i think i asked the question wrong. i would like the vba code that references cell b4 with the text "david" in it to be able to follow that text if i say cut it to cell d6.

so if i insert any columns or rows, the vba knows to follow "david" and not stick with cell b4.

thanks in advance.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
Thats what naming the cell does. For short term situation you could assign a range variable
Code:
Dim myCell As Range

Set myCell = Range("A2")
myCell.Value = "flag"

Range("1:2").Insert shift:=xlDown
Range("A:A").Insert shift:=xlToRight

MsgBox myCell.Address & " " & myCell.Value: Rem "$B$4 flag"
But once the routine runs, myCell passes out of scope and loses its value. If you want the location of your cell to be robust with insertion and deletion between macros, you need to name the range.
 

dtchang

New Member
Joined
Nov 19, 2011
Messages
24

ADVERTISEMENT

Code:
Range("HelloCell").Select


so when that part is in the code, for some reason it continues to selects range b4 and not the cell named "hellocell" even though in the worksheet i can see that if i move the original cell b4 to another location, that new location retains the name "hellocell"

is it because im not telling the vba code that i've moved the cell?

am i just missing something here? apologies in advance.
 

dtchang

New Member
Joined
Nov 19, 2011
Messages
24
In this particular case I'm cut pasting. I'm trying to apply this to a broader piece of code where I will insert rows to make room for new data and I'd like the code to still reference any cells that got moved.

Does the method of moving the cells influence how the code is written?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
Names should work with Cut/Paste and insertions and deletions. Not with Copy/Paste.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top