Macro to shift

U

Unique713

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

Luke M

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 said:
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!*
Click to expand...

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


Unique713 said:
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?
Click to expand...
 
U

Unique713

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 said:
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 said:
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!*
Click to expand...

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


Unique713 said:
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?
Click to expand...
Click to expand...
 
U

Unique713

I tried it again. It worked! Thank you so much! I have been trying to do this
for 2 months. Thanks again!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change data with a macro 8
Macro to shift to next row 2
Macro Flexability 7
How do I edit this macro? 5
I need a simple loop with a 4+ row added in. 5
Runtime 1004 error -- insert method of range class failed. 1
string assistance to open workbook via macro 2
Help with Macro 3

Top