select cell by variable name
 

Hi,

When I insert a row after I have recorded a macro, the macro stops referring
to the correct cells. To fix this, I tried naming all the cells variables
and having the macro refer to the variable names instead of the cell location
(ie. A1).

So instead of:

Range("A1").Select
Selection.Copy

I tried:

Range("pop1d").Select
Selection.Copy

but I get an error message that says "select method of range class failed".
What is the correct way to refer to variable names in visual basic? Also, is
there any other way to have visual basic auto-update and refer to the correct
cells if I were to insert a row after recording the macro?

Thanks,

Adam

select cell by variable name
 

hi
you might try variables
dim r as range
set r = range("D10")
if you add rows above D10, the variable should shift down if that is what
you are trying to achive.

regards
FSt1

"Adam" wrote:

Hi,

When I insert a row after I have recorded a macro, the macro stops referring
to the correct cells. To fix this, I tried naming all the cells variables
and having the macro refer to the variable names instead of the cell location
(ie. A1).

So instead of:

Range("A1").Select
Selection.Copy

I tried:

Range("pop1d").Select
Selection.Copy

but I get an error message that says "select method of range class failed".
What is the correct way to refer to variable names in visual basic? Also, is
there any other way to have visual basic auto-update and refer to the correct
cells if I were to insert a row after recording the macro?

Thanks,

Adam

select cell by variable name
 

I'm still not sure what I'm supposed to do. I tried the code you provided in
VB, but when I inserted a row the code: set r = range("D10") did not
update to: set r = range("D11").

Forgetting about auto-updating the macro, is there any way to select a cell
by the variable name instead of the cell location?

I tried Range("var_name").Select but that does not work.

Thanks,

Adam



"FSt1" wrote:

hi
you might try variables
dim r as range
set r = range("D10")
if you add rows above D10, the variable should shift down if that is what
you are trying to achive.

regards
FSt1

"Adam" wrote:

Hi,

When I insert a row after I have recorded a macro, the macro stops referring
to the correct cells. To fix this, I tried naming all the cells variables
and having the macro refer to the variable names instead of the cell location
(ie. A1).

So instead of:

Range("A1").Select
Selection.Copy

I tried:

Range("pop1d").Select
Selection.Copy

but I get an error message that says "select method of range class failed".
What is the correct way to refer to variable names in visual basic? Also, is
there any other way to have visual basic auto-update and refer to the correct
cells if I were to insert a row after recording the macro?

Thanks,

Adam

select cell by variable name
 

In order to select a range, the worksheet that holds that range must be selected
(or active). In order for the worksheet to be selected, the workbook that holds
that worksheet has to be selected.

So you could use:

Range("pop1d").parent.parent.activate 'workbook
Range("pop1d").parent.select 'worksheet
Range("pop1d").Select 'range

or

application.goto Range("pop1d"), scroll:=true 'or false

This does assume that you really have a range named "pop1d".

=====
There is nothing that I've seen that will adjust your code when you move cells
-- or insert/delete rows/columns.

That's the best thing about naming your ranges. The code shouldn't have to
change.



Adam wrote:

I'm still not sure what I'm supposed to do. I tried the code you provided in
VB, but when I inserted a row the code: set r = range("D10") did not
update to: set r = range("D11").

Forgetting about auto-updating the macro, is there any way to select a cell
by the variable name instead of the cell location?

I tried Range("var_name").Select but that does not work.

Thanks,

Adam

"FSt1" wrote:

hi
you might try variables
dim r as range
set r = range("D10")
if you add rows above D10, the variable should shift down if that is what
you are trying to achive.

regards
FSt1

"Adam" wrote:

Hi,

When I insert a row after I have recorded a macro, the macro stops referring
to the correct cells. To fix this, I tried naming all the cells variables
and having the macro refer to the variable names instead of the cell location
(ie. A1).

So instead of:

Range("A1").Select
Selection.Copy

I tried:

Range("pop1d").Select
Selection.Copy

but I get an error message that says "select method of range class failed".
What is the correct way to refer to variable names in visual basic? Also, is
there any other way to have visual basic auto-update and refer to the correct
cells if I were to insert a row after recording the macro?

Thanks,

Adam

--

Dave Peterson

select cell by variable name
 

hi,
yes you are right, the code did not update. but on the sheet the variable
which was set prior to add row shifted to d11 after the row was added. to
test do this
dim r as range
set r = range("D10")
r.select
rows("6:6").insert shift:=xldown
r.select
msgbox r.address

you will set that though set at D10 prior to add row, it is now selecting
D11 and the variable is now D11 after add row.

Regards
FSt1
"Adam" wrote:

I'm still not sure what I'm supposed to do. I tried the code you provided in
VB, but when I inserted a row the code: set r = range("D10") did not
update to: set r = range("D11").

Forgetting about auto-updating the macro, is there any way to select a cell
by the variable name instead of the cell location?

I tried Range("var_name").Select but that does not work.

Thanks,

Adam



"FSt1" wrote:

hi
you might try variables
dim r as range
set r = range("D10")
if you add rows above D10, the variable should shift down if that is what
you are trying to achive.

regards
FSt1

"Adam" wrote:

Hi,

When I insert a row after I have recorded a macro, the macro stops referring
to the correct cells. To fix this, I tried naming all the cells variables
and having the macro refer to the variable names instead of the cell location
(ie. A1).

So instead of:

Range("A1").Select
Selection.Copy

I tried:

Range("pop1d").Select
Selection.Copy

but I get an error message that says "select method of range class failed".
What is the correct way to refer to variable names in visual basic? Also, is
there any other way to have visual basic auto-update and refer to the correct
cells if I were to insert a row after recording the macro?

Thanks,

Adam

select cell by variable name
 

What doesn't work with that line?

Do you have a range named "var_name" ?

How did you name it?

Works for me in Excel 2003

Try these for testing only.

Sub test()
Range("A10").Select
Selection.Name = "var_name"
Range("A9").Select
Selection.Resize(4).EntireRow.Insert
End Sub

Sub test2()
Range("var_name").Select
msgbox selection.address
End Sub


Gord Dibben MS Excel MVP

On Tue, 20 May 2008 11:02:17 -0700, Adam wrote:

I tried Range("var_name").Select but that does not work.

select cell by variable name
 

In order to select a range, the worksheet that holds that range must be selected
(or active). In order for the worksheet to be selected, the workbook that holds
that worksheet has to be ACTIVATED (typo fixed here).

Dave Peterson wrote:

In order to select a range, the worksheet that holds that range must be selected
(or active). In order for the worksheet to be selected, the workbook that holds
that worksheet has to be selected.

So you could use:

Range("pop1d").parent.parent.activate 'workbook
Range("pop1d").parent.select 'worksheet
Range("pop1d").Select 'range

or

application.goto Range("pop1d"), scroll:=true 'or false

This does assume that you really have a range named "pop1d".

=====
There is nothing that I've seen that will adjust your code when you move cells
-- or insert/delete rows/columns.

That's the best thing about naming your ranges. The code shouldn't have to
change.

Adam wrote:

I'm still not sure what I'm supposed to do. I tried the code you provided in
VB, but when I inserted a row the code: set r = range("D10") did not
update to: set r = range("D11").

Forgetting about auto-updating the macro, is there any way to select a cell
by the variable name instead of the cell location?

I tried Range("var_name").Select but that does not work.

Thanks,

Adam

"FSt1" wrote:

hi
you might try variables
dim r as range
set r = range("D10")
if you add rows above D10, the variable should shift down if that is what
you are trying to achive.

regards
FSt1

"Adam" wrote:

Hi,

When I insert a row after I have recorded a macro, the macro stops referring
to the correct cells. To fix this, I tried naming all the cells variables
and having the macro refer to the variable names instead of the cell location
(ie. A1).

So instead of:

Range("A1").Select
Selection.Copy

I tried:

Range("pop1d").Select
Selection.Copy

but I get an error message that says "select method of range class failed".
What is the correct way to refer to variable names in visual basic? Also, is
there any other way to have visual basic auto-update and refer to the correct
cells if I were to insert a row after recording the macro?

Thanks,

Adam

--

Dave Peterson

--

Dave Peterson