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?
|