Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to shift
Is there any way to to change this macro so that every time it is run the row
is changed? Here is the macro: Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[3]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[0]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[-1]" Range("E6").Select I want to tweak it so that when it is run the product would look like this If I were to write the macro to illustrate the change: Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[4]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[1]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[0]C[-1]" Range("E6").Select And then the next time it is run the product would look like this (As if the macro was written this way) Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[5]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[2]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[1]C[-1]" Range("E6").Select I dont want to have to physically change the row numbers in the macro everytime it is run. I want it to do it on its own. Can anyone help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to shift
The x refers to nothing its just a label. You had already stated you wanted
it to move 1 row, so at the end when it adds 1 to current value in helper cell, next time it runs it will be a 1 row more than the previous time. The "A1" range value should be the number of the row you want to start in. In Excel worksheet, set A1=3, A2 = 0, A3 = -1 For your macro Dim xCount1 as Integer Dim xCount2 as Integer Dim xCount3 as Integer xCount1=Range("A1").value xCount2=Range("A2").value xCount3=Range("A3").value Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[" & xCount1 & "]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[" & xCount2 & "]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[" & xCount3 & "]C[-1]" Range("A1").value = xCount1 + 1 Range("A2").value = xCount2 + 1 Range("A3").value = xCount3 + 1 'All rows will now be 1 more than last run Range("E6").Select Copied from earlier post for reference: I am very new to macros so I am a little confused. Where you put the x would be the number of rows I want it to move everytime the macro is run? Does the "A1" range value equal the cell that i want the information placed in? With the value portion on the bottom, wont that just add to the figure in the cell (if the value of the cell is 4 wont it make it 5)? "Luke M" wrote: You could use a helper cell. something like Dim xCount as Integer xCount = Range("A1").value 'where A1 has initially been set to 3 Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R["&xCount&"]C" Range("A1").value = Range("A1").value + 1 Of course, you could use any cell, on any sheet you want as a helper cell. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Unique713" wrote: Is there any way to to change this macro so that every time it is run the row is changed? Here is the macro: Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[3]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[0]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[-1]" Range("E6").Select I want to tweak it so that when it is run the product would look like this If I were to write the macro to illustrate the change: Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[4]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[1]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[0]C[-1]" Range("E6").Select And then the next time it is run the product would look like this (As if the macro was written this way) Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[5]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[2]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[1]C[-1]" Range("E6").Select I dont want to have to physically change the row numbers in the macro everytime it is run. I want it to do it on its own. Can anyone help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to shift
i copied and pasted what you put in but only the help cells changed. the 3
became a 4 the 0 became a 1 and the -1 became 0. But my data stayed the same. Did I do something wrong? "Luke M" wrote: The x refers to nothing its just a label. You had already stated you wanted it to move 1 row, so at the end when it adds 1 to current value in helper cell, next time it runs it will be a 1 row more than the previous time. The "A1" range value should be the number of the row you want to start in. In Excel worksheet, set A1=3, A2 = 0, A3 = -1 For your macro Dim xCount1 as Integer Dim xCount2 as Integer Dim xCount3 as Integer xCount1=Range("A1").value xCount2=Range("A2").value xCount3=Range("A3").value Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[" & xCount1 & "]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[" & xCount2 & "]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[" & xCount3 & "]C[-1]" Range("A1").value = xCount1 + 1 Range("A2").value = xCount2 + 1 Range("A3").value = xCount3 + 1 'All rows will now be 1 more than last run Range("E6").Select Copied from earlier post for reference: I am very new to macros so I am a little confused. Where you put the x would be the number of rows I want it to move everytime the macro is run? Does the "A1" range value equal the cell that i want the information placed in? With the value portion on the bottom, wont that just add to the figure in the cell (if the value of the cell is 4 wont it make it 5)? "Luke M" wrote: You could use a helper cell. something like Dim xCount as Integer xCount = Range("A1").value 'where A1 has initially been set to 3 Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R["&xCount&"]C" Range("A1").value = Range("A1").value + 1 Of course, you could use any cell, on any sheet you want as a helper cell. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Unique713" wrote: Is there any way to to change this macro so that every time it is run the row is changed? Here is the macro: Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[3]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[0]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[-1]" Range("E6").Select I want to tweak it so that when it is run the product would look like this If I were to write the macro to illustrate the change: Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[4]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[1]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[0]C[-1]" Range("E6").Select And then the next time it is run the product would look like this (As if the macro was written this way) Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[5]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[2]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[1]C[-1]" Range("E6").Select I dont want to have to physically change the row numbers in the macro everytime it is run. I want it to do it on its own. Can anyone help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to shift
I tried it again. It worked! Thank you so much! I have been trying to do this
for 2 months. Thanks again! "Unique713" wrote: Is there any way to to change this macro so that every time it is run the row is changed? Here is the macro: Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[3]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[0]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[-1]C[-1]" Range("E6").Select I want to tweak it so that when it is run the product would look like this If I were to write the macro to illustrate the change: Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[4]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[1]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[0]C[-1]" Range("E6").Select And then the next time it is run the product would look like this (As if the macro was written this way) Range("C1").Select ActiveCell.FormulaR1C1 = "=Master!R[5]C" Range("E4").Select ActiveCell.FormulaR1C1 = "='Riverview East Owners'!R[2]C[-1]" Range("E5").Select ActiveCell.FormulaR1C1 = "='Liberty House Condominium'!R[1]C[-1]" Range("E6").Select I dont want to have to physically change the row numbers in the macro everytime it is run. I want it to do it on its own. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to shift to next row | Excel Worksheet Functions | |||
HELP: Setting CNTL-SHIFT-F to invoke Excel2007 macro | Excel Discussion (Misc queries) | |||
help with VB/Macro to shift cells | Excel Discussion (Misc queries) | |||
help with VB/Macro to shift cells | Excel Discussion (Misc queries) | |||
how to use shift and down arrow keys in a macro? | Excel Worksheet Functions |