Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a range
After I put my data in cell E10, I want to goto cell B15.
What I have here in the procedure in line 3 is the line from a macro I recorded to try to get an idea how to do this. But this line has the value i input during recording. I will need to input a different value on each use of the ws. I expect I need a variable declared to allow cell value to change, but I don't have enough knowledge to know exactly how to do it. I'm thinking that if I declare a variable, then say activecell.value = Myvariable, that might do it - but this is a newbie guess from other code I've been reading. If this is right, does the variable name need to go in the parantheses in the public sub line? Please help Public Sub Worksheet_Activate() Range("E10").Activate ActiveCell.FormulaR1C1 = "34567" Range("B15").Select End Sub thank you Joanne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a range
You don't have to activate or select a cell to read or write data
from Range("E10").Activate ActiveCell.FormulaR1C1 = "34567" to Range("E10").Activate.FormulaR1C1 = "34567" You can move through cells as follows For RowCount = 1 to 10 Range("E" & RowCount).FormulaR1C1 = "34567" next RowCount For ColCount = 3 to 10 cells(3, ColCount).FormulaR1C1 = "34567" next ColCount Note for cells statement you can either use cells(3,5) = 8 or cells(3,"E") = 8 "Joanne" wrote: After I put my data in cell E10, I want to goto cell B15. What I have here in the procedure in line 3 is the line from a macro I recorded to try to get an idea how to do this. But this line has the value i input during recording. I will need to input a different value on each use of the ws. I expect I need a variable declared to allow cell value to change, but I don't have enough knowledge to know exactly how to do it. I'm thinking that if I declare a variable, then say activecell.value = Myvariable, that might do it - but this is a newbie guess from other code I've been reading. If this is right, does the variable name need to go in the parantheses in the public sub line? Please help Public Sub Worksheet_Activate() Range("E10").Activate ActiveCell.FormulaR1C1 = "34567" Range("B15").Select End Sub thank you Joanne |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a range
hi
not really. the variable can be put in parantheses or in can be declared. sub test (r as string) or sub test() dim r as string either way is ok. then in your coding... Public Sub Worksheet_Activate() Range("E10").value = "34567" 'Note the absent Activecell. less typing, cleaner code. Range("B15").Select End Sub you might want to make use of the input box function.... Public Sub Worksheet_Activate() dim r as string r = inputbox("enter something") Range("E10").value = r Range("B15").Select End Sub hope this cleared things up somewhat regards FSt1 "Joanne" wrote: After I put my data in cell E10, I want to goto cell B15. What I have here in the procedure in line 3 is the line from a macro I recorded to try to get an idea how to do this. But this line has the value i input during recording. I will need to input a different value on each use of the ws. I expect I need a variable declared to allow cell value to change, but I don't have enough knowledge to know exactly how to do it. I'm thinking that if I declare a variable, then say activecell.value = Myvariable, that might do it - but this is a newbie guess from other code I've been reading. If this is right, does the variable name need to go in the parantheses in the public sub line? Please help Public Sub Worksheet_Activate() Range("E10").Activate ActiveCell.FormulaR1C1 = "34567" Range("B15").Select End Sub thank you Joanne |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a range
In the module for the relevant sheet, make the following event macro:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$10" Then Range("B15").Select End Sub rgds "Joanne" skrev i meddelelsen ... After I put my data in cell E10, I want to goto cell B15. What I have here in the procedure in line 3 is the line from a macro I recorded to try to get an idea how to do this. But this line has the value i input during recording. I will need to input a different value on each use of the ws. I expect I need a variable declared to allow cell value to change, but I don't have enough knowledge to know exactly how to do it. I'm thinking that if I declare a variable, then say activecell.value = Myvariable, that might do it - but this is a newbie guess from other code I've been reading. If this is right, does the variable name need to go in the parantheses in the public sub line? Please help Public Sub Worksheet_Activate() Range("E10").Activate ActiveCell.FormulaR1C1 = "34567" Range("B15").Select End Sub thank you Joanne |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a range
Works sweet - thank you very much
Also gives me some info on how ws_change works - I am having trouble figuring out where to put my macro calls so they run when they should (and only then). I see using an if statement kinda anchors the code so that it runs only when target.address is = to E10 (in this instance), so the line of code will not run on other change events using a diff address. Am I going down the right path? Lazzzx wrote: In the module for the relevant sheet, make the following event macro: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$10" Then Range("B15").Select End Sub rgds "Joanne" skrev i meddelelsen ... After I put my data in cell E10, I want to goto cell B15. What I have here in the procedure in line 3 is the line from a macro I recorded to try to get an idea how to do this. But this line has the value i input during recording. I will need to input a different value on each use of the ws. I expect I need a variable declared to allow cell value to change, but I don't have enough knowledge to know exactly how to do it. I'm thinking that if I declare a variable, then say activecell.value = Myvariable, that might do it - but this is a newbie guess from other code I've been reading. If this is right, does the variable name need to go in the parantheses in the public sub line? Please help Public Sub Worksheet_Activate() Range("E10").Activate ActiveCell.FormulaR1C1 = "34567" Range("B15").Select End Sub thank you Joanne |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a range
Hi
You are absolutely going down the right path. Shoud be aware that Target.Address returns a string including dollar-signs. If you more advanced conditioning, you can use Target.Row and/or Target.Column. Those properties returns number of row/column of the target. Worksheet_SelectionChange works similar to Worksheet_Change, except the macro is executed whenever selection is changed on the sheet, even if the entered value of the sheet is not changed. Regards, Lazzzx "Joanne" skrev i meddelelsen ... Works sweet - thank you very much Also gives me some info on how ws_change works - I am having trouble figuring out where to put my macro calls so they run when they should (and only then). I see using an if statement kinda anchors the code so that it runs only when target.address is = to E10 (in this instance), so the line of code will not run on other change events using a diff address. Am I going down the right path? Lazzzx wrote: In the module for the relevant sheet, make the following event macro: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$10" Then Range("B15").Select End Sub rgds "Joanne" skrev i meddelelsen ... After I put my data in cell E10, I want to goto cell B15. What I have here in the procedure in line 3 is the line from a macro I recorded to try to get an idea how to do this. But this line has the value i input during recording. I will need to input a different value on each use of the ws. I expect I need a variable declared to allow cell value to change, but I don't have enough knowledge to know exactly how to do it. I'm thinking that if I declare a variable, then say activecell.value = Myvariable, that might do it - but this is a newbie guess from other code I've been reading. If this is right, does the variable name need to go in the parantheses in the public sub line? Please help Public Sub Worksheet_Activate() Range("E10").Activate ActiveCell.FormulaR1C1 = "34567" Range("B15").Select End Sub thank you Joanne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help with selecting a range | Excel Discussion (Misc queries) | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Selecting Range | Excel Programming | |||
Help please in selecting range dependent on another range | Excel Programming | |||
Selecting a Range inside a range | Excel Programming |