How can I loop through a the values in multiple rows

  • Thread starter Thread starter EMarre
  • Start date Start date
E

EMarre

I am trying to print a file with charts for multiple department numbers
listed in rows 11 to 192. I need to move the value of each cell from this
rows one at a time to cell A1. This way the spreadsheet recalculates for each
different department. I wrote the following macro:

Dim i As Integer
i = 11

Do While i < 193
Range("A1").Select
ActiveCell.FormulaR1C1 = "R[1]C"
Range("A2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
i = i + 1
Loop

and substituted the value 1 inside "R[1]C" for the variable i to see if I
could loop through the rows but since it appears to be a string the loop
doesn't work. Any suggestions on how to do this loop will be appreciated.

Thanks
 
Does this mean that the values are in A11:A192 (column A) of the same sheet?

If yes.

dim myCell as range
dim myRng as range

with activesheet
set myrng = .range("A11:A13") 'change A13 to A192 when you're done testing

for each mycell in myrng.cells
.range("A1").value = mycell.value
.printout preview:=true 'just to test
next mycell
End with


I am trying to print a file with charts for multiple department numbers
listed in rows 11 to 192. I need to move the value of each cell from this
rows one at a time to cell A1. This way the spreadsheet recalculates for each
different department. I wrote the following macro:

Dim i As Integer
i = 11

Do While i < 193
Range("A1").Select
ActiveCell.FormulaR1C1 = "R[1]C"
Range("A2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
i = i + 1
Loop

and substituted the value 1 inside "R[1]C" for the variable i to see if I
could loop through the rows but since it appears to be a string the loop
doesn't work. Any suggestions on how to do this loop will be appreciated.

Thanks
Click to expand...
 
I think what you wrote will probably work, you just need to change your
formula to include the = symbol:

ActiveCell.FormulaR1C1 = "=R[" & i & "]C"

which should be the same as typing something like
=R[11]C
directly into the cell.
But! R[11]C actually would refer to cell A12 (if it were in cell A1). You
may want to remove the [] brackets from the formula to get absolute
references, as:
ActiveCell.FormulaR1C1 = "=R" & i & "C"
 
JLatham,

Thanks so much for your quick reply. I try ActiveCell.FormulaR1C1 = "=R[" &
i & "]C" but I get a VB error message saying: Compile Error: Expected end of
statement:

Any other suggestions will be greatly appreciated.

Thanks again,


JLatham said:
I think what you wrote will probably work, you just need to change your
formula to include the = symbol:

ActiveCell.FormulaR1C1 = "=R[" & i & "]C"

which should be the same as typing something like
=R[11]C
directly into the cell.
But! R[11]C actually would refer to cell A12 (if it were in cell A1). You
may want to remove the [] brackets from the formula to get absolute
references, as:
ActiveCell.FormulaR1C1 = "=R" & i & "C"


EMarre said:
I am trying to print a file with charts for multiple department numbers
listed in rows 11 to 192. I need to move the value of each cell from this
rows one at a time to cell A1. This way the spreadsheet recalculates for each
different department. I wrote the following macro:

Dim i As Integer
i = 11

Do While i < 193
Range("A1").Select
ActiveCell.FormulaR1C1 = "R[1]C"
Range("A2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
i = i + 1
Loop

and substituted the value 1 inside "R[1]C" for the variable i to see if I
could loop through the rows but since it appears to be a string the loop
doesn't work. Any suggestions on how to do this loop will be appreciated.

Thanks
Click to expand...
Click to expand...
 
First, take a look at Dave Peterson's post - he may have something for you.
It's doing essentially the same thing in a different way.

In the meanwhile, I checked, and the little code snippet you put up does
compile correctly on my system. When you get that error message, hit the
[Debug] button and it will take you into the code either to the offending
line of code. Make sure the formula is all on one line in the code module,
not split across 2 lines.

EMarre said:
JLatham,

Thanks so much for your quick reply. I try ActiveCell.FormulaR1C1 = "=R[" &
i & "]C" but I get a VB error message saying: Compile Error: Expected end of
statement:

Any other suggestions will be greatly appreciated.

Thanks again,


JLatham said:
I think what you wrote will probably work, you just need to change your
formula to include the = symbol:

ActiveCell.FormulaR1C1 = "=R[" & i & "]C"

which should be the same as typing something like
=R[11]C
directly into the cell.
But! R[11]C actually would refer to cell A12 (if it were in cell A1). You
may want to remove the [] brackets from the formula to get absolute
references, as:
ActiveCell.FormulaR1C1 = "=R" & i & "C"


EMarre said:
I am trying to print a file with charts for multiple department numbers
listed in rows 11 to 192. I need to move the value of each cell from this
rows one at a time to cell A1. This way the spreadsheet recalculates for each
different department. I wrote the following macro:

Dim i As Integer
i = 11

Do While i < 193
Range("A1").Select
ActiveCell.FormulaR1C1 = "R[1]C"
Range("A2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
i = i + 1
Loop

and substituted the value 1 inside "R[1]C" for the variable i to see if I
could loop through the rows but since it appears to be a string the loop
doesn't work. Any suggestions on how to do this loop will be appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Dave,

Thanks so much for your help. It works perfectly!

Regards,

EMarre

Dave Peterson said:
Does this mean that the values are in A11:A192 (column A) of the same sheet?

If yes.

dim myCell as range
dim myRng as range

with activesheet
set myrng = .range("A11:A13") 'change A13 to A192 when you're done testing

for each mycell in myrng.cells
.range("A1").value = mycell.value
.printout preview:=true 'just to test
next mycell
End with


I am trying to print a file with charts for multiple department numbers
listed in rows 11 to 192. I need to move the value of each cell from this
rows one at a time to cell A1. This way the spreadsheet recalculates for each
different department. I wrote the following macro:

Dim i As Integer
i = 11

Do While i < 193
Range("A1").Select
ActiveCell.FormulaR1C1 = "R[1]C"
Range("A2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
i = i + 1
Loop

and substituted the value 1 inside "R[1]C" for the variable i to see if I
could loop through the rows but since it appears to be a string the loop
doesn't work. Any suggestions on how to do this loop will be appreciated.

Thanks
Click to expand...
Click to expand...
 
JLatham,

Thanks so much for your help. You guys are really fast and know your stuff
very well. As suggested I try Dave recommendation and it works but sincerely
appreciate your time.

Regards,

EMarre

JLatham said:
First, take a look at Dave Peterson's post - he may have something for you.
It's doing essentially the same thing in a different way.

In the meanwhile, I checked, and the little code snippet you put up does
compile correctly on my system. When you get that error message, hit the
[Debug] button and it will take you into the code either to the offending
line of code. Make sure the formula is all on one line in the code module,
not split across 2 lines.

EMarre said:
JLatham,

Thanks so much for your quick reply. I try ActiveCell.FormulaR1C1 = "=R[" &
i & "]C" but I get a VB error message saying: Compile Error: Expected end of
statement:

Any other suggestions will be greatly appreciated.

Thanks again,


JLatham said:
I think what you wrote will probably work, you just need to change your
formula to include the = symbol:

ActiveCell.FormulaR1C1 = "=R[" & i & "]C"

which should be the same as typing something like
=R[11]C
directly into the cell.
But! R[11]C actually would refer to cell A12 (if it were in cell A1). You
may want to remove the [] brackets from the formula to get absolute
references, as:
ActiveCell.FormulaR1C1 = "=R" & i & "C"


:

I am trying to print a file with charts for multiple department numbers
listed in rows 11 to 192. I need to move the value of each cell from this
rows one at a time to cell A1. This way the spreadsheet recalculates for each
different department. I wrote the following macro:

Dim i As Integer
i = 11

Do While i < 193
Range("A1").Select
ActiveCell.FormulaR1C1 = "R[1]C"
Range("A2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
i = i + 1
Loop

and substituted the value 1 inside "R[1]C" for the variable i to see if I
could loop through the rows but since it appears to be a string the loop
doesn't work. Any suggestions on how to do this loop will be appreciated.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top