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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
The easiest way is to name the range
Code:
Range("$B$4").Name = "HelloCell"
To use it again
Code:
Range("HelloCell").Select
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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
Back
Top