Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using formulas to select cells (Ex: Select every nth cell in a col | Excel Discussion (Misc queries) | |||
Use a Variable to select a range | Excel Discussion (Misc queries) | |||
Select one variable from a list of variables by clicking one cell | Excel Discussion (Misc queries) | |||
Excel 2003: In a Macro,how to select a variable row length table | Excel Discussion (Misc queries) | |||
Range("C9:V9").Select ==> changing the 9 to a variable | Excel Worksheet Functions |