If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I loop through a the values in multiple rows
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 |
#2
|
|||
|
|||
How can I loop through a the values in multiple rows
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 EMarre wrote: 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 -- Dave Peterson |
#3
|
|||
|
|||
How can I loop through a the values in multiple rows
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" wrote: 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 |
#4
|
|||
|
|||
How can I loop through a the values in multiple rows
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" wrote: 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" wrote: 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 |
#5
|
|||
|
|||
How can I loop through a the values in multiple rows
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" wrote: 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" wrote: 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" wrote: 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 |
#6
|
|||
|
|||
How can I loop through a the values in multiple rows
Dave,
Thanks so much for your help. It works perfectly! Regards, EMarre "Dave Peterson" wrote: 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 EMarre wrote: 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 -- Dave Peterson . |
#7
|
|||
|
|||
How can I loop through a the values in multiple rows
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|