Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
I have a spreadsheet where have a set model and then I have three columns of
variable data. The three variables need to be put into the designated input cells for the model to produce an answer. I need some help writing a macro which takes the three variables in one go and copies the values into the correct cells, then copies the answer into a fourth column next to the variables, then the macro should move down a row and repeat with the new variables from that row. For example, copy P5 into E11, copy Q5 into E14, copy R5 into E19 the take the answer from cell E20 and paste into S5, then drop down so that copy P6 into E11, copy Q6 into E14, copy R6 into E19 the take the answer from cell E20 and paste into S6 - repeat down to row 2000. Any help would be greatly appreciated! Thanks in advance Nick |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
Copying is simple
Range("P5").Copy Range("E11") etc. Getting an answer is a tad ambiguous, it could be just summing, maybe multiple, but you don't need to put in cells, you can do it in the macro Range("S5").Vale = Application.SUM(Range("P5:R5") etc. Do you want to loop through all cells in column P and load S accordingly? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "NICK" wrote in message ... I have a spreadsheet where have a set model and then I have three columns of variable data. The three variables need to be put into the designated input cells for the model to produce an answer. I need some help writing a macro which takes the three variables in one go and copies the values into the correct cells, then copies the answer into a fourth column next to the variables, then the macro should move down a row and repeat with the new variables from that row. For example, copy P5 into E11, copy Q5 into E14, copy R5 into E19 the take the answer from cell E20 and paste into S5, then drop down so that copy P6 into E11, copy Q6 into E14, copy R6 into E19 the take the answer from cell E20 and paste into S6 - repeat down to row 2000. Any help would be greatly appreciated! Thanks in advance Nick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
Hi Bob
Perhaps I didn't explain myself properly. I have three colums of data (Columns P, Q & R). I need to copy all three variables from one row into designated input cells (Cells E11, E14 & E19) then i want to copy the result (Cell E20) as values into Column S. Once I have run one set of variables through the model and pasted the answer, i want to move down one row and do it again, then again and so on for 2,000 rows. Is that clearer? "Bob Phillips" wrote: Copying is simple Range("P5").Copy Range("E11") etc. Getting an answer is a tad ambiguous, it could be just summing, maybe multiple, but you don't need to put in cells, you can do it in the macro Range("S5").Vale = Application.SUM(Range("P5:R5") etc. Do you want to loop through all cells in column P and load S accordingly? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "NICK" wrote in message ... I have a spreadsheet where have a set model and then I have three columns of variable data. The three variables need to be put into the designated input cells for the model to produce an answer. I need some help writing a macro which takes the three variables in one go and copies the values into the correct cells, then copies the answer into a fourth column next to the variables, then the macro should move down a row and repeat with the new variables from that row. For example, copy P5 into E11, copy Q5 into E14, copy R5 into E19 the take the answer from cell E20 and paste into S5, then drop down so that copy P6 into E11, copy Q6 into E14, copy R6 into E19 the take the answer from cell E20 and paste into S6 - repeat down to row 2000. Any help would be greatly appreciated! Thanks in advance Nick |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
I think I got all that. My point was that there is little logic to writing
to E11, E14 and E19, then overwriting it in the next iteration of the loop, and also what is meant by result? Is there a formula in E20, if so, why not just replicate in the code and avoid the (slow) writing to cells. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "NICK" wrote in message ... Hi Bob Perhaps I didn't explain myself properly. I have three colums of data (Columns P, Q & R). I need to copy all three variables from one row into designated input cells (Cells E11, E14 & E19) then i want to copy the result (Cell E20) as values into Column S. Once I have run one set of variables through the model and pasted the answer, i want to move down one row and do it again, then again and so on for 2,000 rows. Is that clearer? "Bob Phillips" wrote: Copying is simple Range("P5").Copy Range("E11") etc. Getting an answer is a tad ambiguous, it could be just summing, maybe multiple, but you don't need to put in cells, you can do it in the macro Range("S5").Vale = Application.SUM(Range("P5:R5") etc. Do you want to loop through all cells in column P and load S accordingly? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "NICK" wrote in message ... I have a spreadsheet where have a set model and then I have three columns of variable data. The three variables need to be put into the designated input cells for the model to produce an answer. I need some help writing a macro which takes the three variables in one go and copies the values into the correct cells, then copies the answer into a fourth column next to the variables, then the macro should move down a row and repeat with the new variables from that row. For example, copy P5 into E11, copy Q5 into E14, copy R5 into E19 the take the answer from cell E20 and paste into S5, then drop down so that copy P6 into E11, copy Q6 into E14, copy R6 into E19 the take the answer from cell E20 and paste into S6 - repeat down to row 2000. Any help would be greatly appreciated! Thanks in advance Nick |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
Bob - you're exactly right however, the formula in E20 is relatively
complicated because it is an IRR calculation. The dates and cashflows for the IRR are driven by the three input cells. So you basically need to solve for one solution using those three variables and then paste the result as a value then move on to the next row of variables. The result in E20 is "=NOMINAL(XIRR(L24:L35,B24:B35),2)-X5". Still interested in your thoughts as it is not a simple sum or multiplication. With thanks Nick "Bob Phillips" wrote: I think I got all that. My point was that there is little logic to writing to E11, E14 and E19, then overwriting it in the next iteration of the loop, and also what is meant by result? Is there a formula in E20, if so, why not just replicate in the code and avoid the (slow) writing to cells. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "NICK" wrote in message ... Hi Bob Perhaps I didn't explain myself properly. I have three colums of data (Columns P, Q & R). I need to copy all three variables from one row into designated input cells (Cells E11, E14 & E19) then i want to copy the result (Cell E20) as values into Column S. Once I have run one set of variables through the model and pasted the answer, i want to move down one row and do it again, then again and so on for 2,000 rows. Is that clearer? "Bob Phillips" wrote: Copying is simple Range("P5").Copy Range("E11") etc. Getting an answer is a tad ambiguous, it could be just summing, maybe multiple, but you don't need to put in cells, you can do it in the macro Range("S5").Vale = Application.SUM(Range("P5:R5") etc. Do you want to loop through all cells in column P and load S accordingly? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "NICK" wrote in message ... I have a spreadsheet where have a set model and then I have three columns of variable data. The three variables need to be put into the designated input cells for the model to produce an answer. I need some help writing a macro which takes the three variables in one go and copies the values into the correct cells, then copies the answer into a fourth column next to the variables, then the macro should move down a row and repeat with the new variables from that row. For example, copy P5 into E11, copy Q5 into E14, copy R5 into E19 the take the answer from cell E20 and paste into S5, then drop down so that copy P6 into E11, copy Q6 into E14, copy R6 into E19 the take the answer from cell E20 and paste into S6 - repeat down to row 2000. Any help would be greatly appreciated! Thanks in advance Nick |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro help
Okay, I thought that might be your response <G.
Does this work for you Public Sub test() Dim iLastRow As Long Dim i As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row For i = 1 To iLastRow 'iLastRow To 1 Step -1 Cells(i, "P").Copy Range("E11") Cells(i, "Q").Copy Range("E14") Cells(i, "R").Copy Range("E19") Cells(i, "S").Value Range("E20").Value Next i End With End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "NICK" wrote in message ... Bob - you're exactly right however, the formula in E20 is relatively complicated because it is an IRR calculation. The dates and cashflows for the IRR are driven by the three input cells. So you basically need to solve for one solution using those three variables and then paste the result as a value then move on to the next row of variables. The result in E20 is "=NOMINAL(XIRR(L24:L35,B24:B35),2)-X5". Still interested in your thoughts as it is not a simple sum or multiplication. With thanks Nick "Bob Phillips" wrote: I think I got all that. My point was that there is little logic to writing to E11, E14 and E19, then overwriting it in the next iteration of the loop, and also what is meant by result? Is there a formula in E20, if so, why not just replicate in the code and avoid the (slow) writing to cells. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "NICK" wrote in message ... Hi Bob Perhaps I didn't explain myself properly. I have three colums of data (Columns P, Q & R). I need to copy all three variables from one row into designated input cells (Cells E11, E14 & E19) then i want to copy the result (Cell E20) as values into Column S. Once I have run one set of variables through the model and pasted the answer, i want to move down one row and do it again, then again and so on for 2,000 rows. Is that clearer? "Bob Phillips" wrote: Copying is simple Range("P5").Copy Range("E11") etc. Getting an answer is a tad ambiguous, it could be just summing, maybe multiple, but you don't need to put in cells, you can do it in the macro Range("S5").Vale = Application.SUM(Range("P5:R5") etc. Do you want to loop through all cells in column P and load S accordingly? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "NICK" wrote in message ... I have a spreadsheet where have a set model and then I have three columns of variable data. The three variables need to be put into the designated input cells for the model to produce an answer. I need some help writing a macro which takes the three variables in one go and copies the values into the correct cells, then copies the answer into a fourth column next to the variables, then the macro should move down a row and repeat with the new variables from that row. For example, copy P5 into E11, copy Q5 into E14, copy R5 into E19 the take the answer from cell E20 and paste into S5, then drop down so that copy P6 into E11, copy Q6 into E14, copy R6 into E19 the take the answer from cell E20 and paste into S6 - repeat down to row 2000. Any help would be greatly appreciated! Thanks in advance Nick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Highlight Range - wrong macro, please edit. | Excel Worksheet Functions |