|
VBA code not working- repost to further explain problem
My macro shoudl work as follows: i have 2 sheets, AG and Summary. On
the AG sheet, I have 20 short list funds (starting in cell A76) that I want to add in to the current portfolio at a 5% weighting (which is in column D), and then I calculate the new volatility, return, beta to teh S&P and correlation to the S&P. The Summary sheet links to the AG sheet and to the specific cells that contain these new results, so for example C4 on Summary links to the new volatility of the portfolio with the short list fund, G4 links to the new return, O4 links to the new beta and S4 links to the new correl. Now I want to take these numbers (say C4) and copy paste special the value to D4. Then it shd go back, make the % of the first short list fund equla to 0% and the second fund should now be equal to 5%. THe links are still the same so now I want to copy, pastespecial C4 to D5, and so on. The code below does not loop through (stays on AG.D76, when it shd go to AG.D77 and so on). It also goes to cell C3 in the summary worksheet instaed of C4 and doesnt seem to be doing any pasting. Is this code completely wrong? ANy suggestions? Dim i As Integer For i = 0 To 20 Do Until i = 20 Application.GoTo Sheets("AG").Range("A76") 'this needs to reflect where list starts ActiveCell.Offset(0 + i, 3).Select 'weighting starts in column D76, then goes to D77, etc ActiveCell.FormulaR1C1 = "5%" 'changes weight from 0 to 5% Application.GoTo Sheets("Summary").Range("C4") Selection.Copy 'copy volatility to adjacent cell ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Application.GoTo Sheets("Summary").Range("G4") 'copy return Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("O4") 'copy beta Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("S4") 'copy correlation Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("AG").Range("A76") ActiveCell.Offset(0 + i, 3).Select ActiveCell.FormulaR1C1 = "0%" 'change D76 back to 0% Loop ' go to D77 and repeat actions up until D95 Next i End Sub |
VBA code not working- repost to further explain problem
I am not sure if this is a problem, but whenever we do:
i = 0 ActiveCell(0 + i, 1).Select we move the active cell upwards towards the top of the worksheet. -- Gary''s Student " wrote: My macro shoudl work as follows: i have 2 sheets, AG and Summary. On the AG sheet, I have 20 short list funds (starting in cell A76) that I want to add in to the current portfolio at a 5% weighting (which is in column D), and then I calculate the new volatility, return, beta to teh S&P and correlation to the S&P. The Summary sheet links to the AG sheet and to the specific cells that contain these new results, so for example C4 on Summary links to the new volatility of the portfolio with the short list fund, G4 links to the new return, O4 links to the new beta and S4 links to the new correl. Now I want to take these numbers (say C4) and copy paste special the value to D4. Then it shd go back, make the % of the first short list fund equla to 0% and the second fund should now be equal to 5%. THe links are still the same so now I want to copy, pastespecial C4 to D5, and so on. The code below does not loop through (stays on AG.D76, when it shd go to AG.D77 and so on). It also goes to cell C3 in the summary worksheet instaed of C4 and doesnt seem to be doing any pasting. Is this code completely wrong? ANy suggestions? Dim i As Integer For i = 0 To 20 Do Until i = 20 Application.GoTo Sheets("AG").Range("A76") 'this needs to reflect where list starts ActiveCell.Offset(0 + i, 3).Select 'weighting starts in column D76, then goes to D77, etc ActiveCell.FormulaR1C1 = "5%" 'changes weight from 0 to 5% Application.GoTo Sheets("Summary").Range("C4") Selection.Copy 'copy volatility to adjacent cell ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Application.GoTo Sheets("Summary").Range("G4") 'copy return Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("O4") 'copy beta Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("S4") 'copy correlation Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("AG").Range("A76") ActiveCell.Offset(0 + i, 3).Select ActiveCell.FormulaR1C1 = "0%" 'change D76 back to 0% Loop ' go to D77 and repeat actions up until D95 Next i End Sub |
VBA code not working- repost to further explain problem
First of all, I think you only need one loop to do this. Secondly, if the
locations of your inputs and outputs are going to be in known places, you'd probably be better off using the .Cells() function. I don't quite understand the specifics of what you're trying to accomplish, but I'm going to take a stab at it. lngResultsStartingRow = 4 ' Row 4 on your "Summary" tab For R = 76 To 95 ' The function uses the format .Cells(rownumber, columnumber), so 4 would correspond to column D Worksheets("AG").Cells(R,4).Value = .05 ' Do some calculations vntAnswer = Worksheets("AG").Cells(R,5).Value ' 5 = column G ' Take the answer and put it in the appropriate cell of the summary tab Worksheets("Summary").Cells(lngResultsStartingRow, 3).Value = vntAnswer ' 3 = Column C ' Repeat process for other calculations ' Set the value of current row back to 0 Worksheets("AG").Cells(R,4).Value = .05 ' Move down a row on the results tab lngResultsStartingRow = lngResultsStartingRow + 1 Next Hope that gets you in the ballpark. -- Hmm...they have the Internet on COMPUTERS now! " wrote: My macro shoudl work as follows: i have 2 sheets, AG and Summary. On the AG sheet, I have 20 short list funds (starting in cell A76) that I want to add in to the current portfolio at a 5% weighting (which is in column D), and then I calculate the new volatility, return, beta to teh S&P and correlation to the S&P. The Summary sheet links to the AG sheet and to the specific cells that contain these new results, so for example C4 on Summary links to the new volatility of the portfolio with the short list fund, G4 links to the new return, O4 links to the new beta and S4 links to the new correl. Now I want to take these numbers (say C4) and copy paste special the value to D4. Then it shd go back, make the % of the first short list fund equla to 0% and the second fund should now be equal to 5%. THe links are still the same so now I want to copy, pastespecial C4 to D5, and so on. The code below does not loop through (stays on AG.D76, when it shd go to AG.D77 and so on). It also goes to cell C3 in the summary worksheet instaed of C4 and doesnt seem to be doing any pasting. Is this code completely wrong? ANy suggestions? Dim i As Integer For i = 0 To 20 Do Until i = 20 Application.GoTo Sheets("AG").Range("A76") 'this needs to reflect where list starts ActiveCell.Offset(0 + i, 3).Select 'weighting starts in column D76, then goes to D77, etc ActiveCell.FormulaR1C1 = "5%" 'changes weight from 0 to 5% Application.GoTo Sheets("Summary").Range("C4") Selection.Copy 'copy volatility to adjacent cell ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Application.GoTo Sheets("Summary").Range("G4") 'copy return Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("O4") 'copy beta Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("S4") 'copy correlation Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("AG").Range("A76") ActiveCell.Offset(0 + i, 3).Select ActiveCell.FormulaR1C1 = "0%" 'change D76 back to 0% Loop ' go to D77 and repeat actions up until D95 Next i End Sub |
VBA code not working- repost to further explain problem
Sorry, it should be:
' Set the value of current row back to 0 Worksheets("AG").Cells(R,4).Value = 0 -- Hmm...they have the Internet on COMPUTERS now! "MDW" wrote: First of all, I think you only need one loop to do this. Secondly, if the locations of your inputs and outputs are going to be in known places, you'd probably be better off using the .Cells() function. I don't quite understand the specifics of what you're trying to accomplish, but I'm going to take a stab at it. lngResultsStartingRow = 4 ' Row 4 on your "Summary" tab For R = 76 To 95 ' The function uses the format .Cells(rownumber, columnumber), so 4 would correspond to column D Worksheets("AG").Cells(R,4).Value = .05 ' Do some calculations vntAnswer = Worksheets("AG").Cells(R,5).Value ' 5 = column G ' Take the answer and put it in the appropriate cell of the summary tab Worksheets("Summary").Cells(lngResultsStartingRow, 3).Value = vntAnswer ' 3 = Column C ' Repeat process for other calculations ' Set the value of current row back to 0 Worksheets("AG").Cells(R,4).Value = .05 ' Move down a row on the results tab lngResultsStartingRow = lngResultsStartingRow + 1 Next Hope that gets you in the ballpark. -- Hmm...they have the Internet on COMPUTERS now! " wrote: My macro shoudl work as follows: i have 2 sheets, AG and Summary. On the AG sheet, I have 20 short list funds (starting in cell A76) that I want to add in to the current portfolio at a 5% weighting (which is in column D), and then I calculate the new volatility, return, beta to teh S&P and correlation to the S&P. The Summary sheet links to the AG sheet and to the specific cells that contain these new results, so for example C4 on Summary links to the new volatility of the portfolio with the short list fund, G4 links to the new return, O4 links to the new beta and S4 links to the new correl. Now I want to take these numbers (say C4) and copy paste special the value to D4. Then it shd go back, make the % of the first short list fund equla to 0% and the second fund should now be equal to 5%. THe links are still the same so now I want to copy, pastespecial C4 to D5, and so on. The code below does not loop through (stays on AG.D76, when it shd go to AG.D77 and so on). It also goes to cell C3 in the summary worksheet instaed of C4 and doesnt seem to be doing any pasting. Is this code completely wrong? ANy suggestions? Dim i As Integer For i = 0 To 20 Do Until i = 20 Application.GoTo Sheets("AG").Range("A76") 'this needs to reflect where list starts ActiveCell.Offset(0 + i, 3).Select 'weighting starts in column D76, then goes to D77, etc ActiveCell.FormulaR1C1 = "5%" 'changes weight from 0 to 5% Application.GoTo Sheets("Summary").Range("C4") Selection.Copy 'copy volatility to adjacent cell ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Application.GoTo Sheets("Summary").Range("G4") 'copy return Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("O4") 'copy beta Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("S4") 'copy correlation Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("AG").Range("A76") ActiveCell.Offset(0 + i, 3).Select ActiveCell.FormulaR1C1 = "0%" 'change D76 back to 0% Loop ' go to D77 and repeat actions up until D95 Next i End Sub |
VBA code not working- repost to further explain problem
ahhh, did not know that...very helpful. Thx!@
Gary''s Student wrote: I am not sure if this is a problem, but whenever we do: i = 0 ActiveCell(0 + i, 1).Select we move the active cell upwards towards the top of the worksheet. -- Gary''s Student " wrote: My macro shoudl work as follows: i have 2 sheets, AG and Summary. On the AG sheet, I have 20 short list funds (starting in cell A76) that I want to add in to the current portfolio at a 5% weighting (which is in column D), and then I calculate the new volatility, return, beta to teh S&P and correlation to the S&P. The Summary sheet links to the AG sheet and to the specific cells that contain these new results, so for example C4 on Summary links to the new volatility of the portfolio with the short list fund, G4 links to the new return, O4 links to the new beta and S4 links to the new correl. Now I want to take these numbers (say C4) and copy paste special the value to D4. Then it shd go back, make the % of the first short list fund equla to 0% and the second fund should now be equal to 5%. THe links are still the same so now I want to copy, pastespecial C4 to D5, and so on. The code below does not loop through (stays on AG.D76, when it shd go to AG.D77 and so on). It also goes to cell C3 in the summary worksheet instaed of C4 and doesnt seem to be doing any pasting. Is this code completely wrong? ANy suggestions? Dim i As Integer For i = 0 To 20 Do Until i = 20 Application.GoTo Sheets("AG").Range("A76") 'this needs to reflect where list starts ActiveCell.Offset(0 + i, 3).Select 'weighting starts in column D76, then goes to D77, etc ActiveCell.FormulaR1C1 = "5%" 'changes weight from 0 to 5% Application.GoTo Sheets("Summary").Range("C4") Selection.Copy 'copy volatility to adjacent cell ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Application.GoTo Sheets("Summary").Range("G4") 'copy return Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("O4") 'copy beta Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("S4") 'copy correlation Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("AG").Range("A76") ActiveCell.Offset(0 + i, 3).Select ActiveCell.FormulaR1C1 = "0%" 'change D76 back to 0% Loop ' go to D77 and repeat actions up until D95 Next i End Sub |
VBA code not working- repost to further explain problem
this looks like it could work. just a few questions that might help
clarify this further. after I add in the Worksheets("AG").Cells(R, 4).Value = 0.05 line, the cell on the summary sheet C4 links to a static cell. The value within the cell, however, is changing as the cell that has the 5% in it changes. Therefore, on the summary sheet, the formula of C4 is static (=AG!CX191: where AGCX191 is calculated from a bunch of other numbers, including the 0% or 5% weight), but the value changes. Therefore, I want to copy, paste special the value into D4, then when the next fund is allocated 5%, that value should be copy pasted from C4 into D5, and so on. Does that make sense? I've been trying to play with it to get that to work, but havent yet figured it out (not very adept at VBA yet). Any help would be greatly appreciated... MDW wrote: First of all, I think you only need one loop to do this. Secondly, if the locations of your inputs and outputs are going to be in known places, you'd probably be better off using the .Cells() function. I don't quite understand the specifics of what you're trying to accomplish, but I'm going to take a stab at it. lngResultsStartingRow = 4 ' Row 4 on your "Summary" tab For R = 76 To 95 ' The function uses the format .Cells(rownumber, columnumber), so 4 would correspond to column D Worksheets("AG").Cells(R,4).Value = .05 ' Do some calculations vntAnswer = Worksheets("AG").Cells(R,5).Value ' 5 = column G ' Take the answer and put it in the appropriate cell of the summary tab Worksheets("Summary").Cells(lngResultsStartingRow, 3).Value = vntAnswer ' 3 = Column C ' Repeat process for other calculations ' Set the value of current row back to 0 Worksheets("AG").Cells(R,4).Value = .05 ' Move down a row on the results tab lngResultsStartingRow = lngResultsStartingRow + 1 Next Hope that gets you in the ballpark. -- Hmm...they have the Internet on COMPUTERS now! " wrote: My macro shoudl work as follows: i have 2 sheets, AG and Summary. On the AG sheet, I have 20 short list funds (starting in cell A76) that I want to add in to the current portfolio at a 5% weighting (which is in column D), and then I calculate the new volatility, return, beta to teh S&P and correlation to the S&P. The Summary sheet links to the AG sheet and to the specific cells that contain these new results, so for example C4 on Summary links to the new volatility of the portfolio with the short list fund, G4 links to the new return, O4 links to the new beta and S4 links to the new correl. Now I want to take these numbers (say C4) and copy paste special the value to D4. Then it shd go back, make the % of the first short list fund equla to 0% and the second fund should now be equal to 5%. THe links are still the same so now I want to copy, pastespecial C4 to D5, and so on. The code below does not loop through (stays on AG.D76, when it shd go to AG.D77 and so on). It also goes to cell C3 in the summary worksheet instaed of C4 and doesnt seem to be doing any pasting. Is this code completely wrong? ANy suggestions? Dim i As Integer For i = 0 To 20 Do Until i = 20 Application.GoTo Sheets("AG").Range("A76") 'this needs to reflect where list starts ActiveCell.Offset(0 + i, 3).Select 'weighting starts in column D76, then goes to D77, etc ActiveCell.FormulaR1C1 = "5%" 'changes weight from 0 to 5% Application.GoTo Sheets("Summary").Range("C4") Selection.Copy 'copy volatility to adjacent cell ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Application.GoTo Sheets("Summary").Range("G4") 'copy return Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("O4") 'copy beta Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("S4") 'copy correlation Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("AG").Range("A76") ActiveCell.Offset(0 + i, 3).Select ActiveCell.FormulaR1C1 = "0%" 'change D76 back to 0% Loop ' go to D77 and repeat actions up until D95 Next i End Sub |
VBA code not working- repost to further explain problem
OK, I think I understand you better now.
So you're always going to get an answer from what the formula in C4 calculates? And you want it to be populated in D5, D6, D7, D8, etc. as you step through the various funds? So let's try this: lngResults = 4 ' Row 4 on the "Summary" tab For R = 76 To 95 ' The function uses the format .Cells(rownumber, columnumber), so 4 would ' correspond to column D Worksheets("AG").Cells(R,4).Value = .05 ' Get the value in C4 vntAnswer = Worksheets("Summary").Range("C4").Value ' Put the answer in the appropriate cell Worksheets("Summary").Cells(lngResults,4).Value = vntAnswer ' 4 - column D ' Set the value of current row back to 0 Worksheets("AG").Cells(R,4).Value = 0 ' Move down a row lngResults = lngResults + 1 Next -- Hmm...they have the Internet on COMPUTERS now! " wrote: this looks like it could work. just a few questions that might help clarify this further. after I add in the Worksheets("AG").Cells(R, 4).Value = 0.05 line, the cell on the summary sheet C4 links to a static cell. The value within the cell, however, is changing as the cell that has the 5% in it changes. Therefore, on the summary sheet, the formula of C4 is static (=AG!CX191: where AGCX191 is calculated from a bunch of other numbers, including the 0% or 5% weight), but the value changes. Therefore, I want to copy, paste special the value into D4, then when the next fund is allocated 5%, that value should be copy pasted from C4 into D5, and so on. Does that make sense? I've been trying to play with it to get that to work, but havent yet figured it out (not very adept at VBA yet). Any help would be greatly appreciated... MDW wrote: First of all, I think you only need one loop to do this. Secondly, if the locations of your inputs and outputs are going to be in known places, you'd probably be better off using the .Cells() function. I don't quite understand the specifics of what you're trying to accomplish, but I'm going to take a stab at it. lngResultsStartingRow = 4 ' Row 4 on your "Summary" tab For R = 76 To 95 ' The function uses the format .Cells(rownumber, columnumber), so 4 would correspond to column D Worksheets("AG").Cells(R,4).Value = .05 ' Do some calculations vntAnswer = Worksheets("AG").Cells(R,5).Value ' 5 = column G ' Take the answer and put it in the appropriate cell of the summary tab Worksheets("Summary").Cells(lngResultsStartingRow, 3).Value = vntAnswer ' 3 = Column C ' Repeat process for other calculations ' Set the value of current row back to 0 Worksheets("AG").Cells(R,4).Value = .05 ' Move down a row on the results tab lngResultsStartingRow = lngResultsStartingRow + 1 Next Hope that gets you in the ballpark. -- Hmm...they have the Internet on COMPUTERS now! " wrote: My macro shoudl work as follows: i have 2 sheets, AG and Summary. On the AG sheet, I have 20 short list funds (starting in cell A76) that I want to add in to the current portfolio at a 5% weighting (which is in column D), and then I calculate the new volatility, return, beta to teh S&P and correlation to the S&P. The Summary sheet links to the AG sheet and to the specific cells that contain these new results, so for example C4 on Summary links to the new volatility of the portfolio with the short list fund, G4 links to the new return, O4 links to the new beta and S4 links to the new correl. Now I want to take these numbers (say C4) and copy paste special the value to D4. Then it shd go back, make the % of the first short list fund equla to 0% and the second fund should now be equal to 5%. THe links are still the same so now I want to copy, pastespecial C4 to D5, and so on. The code below does not loop through (stays on AG.D76, when it shd go to AG.D77 and so on). It also goes to cell C3 in the summary worksheet instaed of C4 and doesnt seem to be doing any pasting. Is this code completely wrong? ANy suggestions? Dim i As Integer For i = 0 To 20 Do Until i = 20 Application.GoTo Sheets("AG").Range("A76") 'this needs to reflect where list starts ActiveCell.Offset(0 + i, 3).Select 'weighting starts in column D76, then goes to D77, etc ActiveCell.FormulaR1C1 = "5%" 'changes weight from 0 to 5% Application.GoTo Sheets("Summary").Range("C4") Selection.Copy 'copy volatility to adjacent cell ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Application.GoTo Sheets("Summary").Range("G4") 'copy return Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("O4") 'copy beta Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("S4") 'copy correlation Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("AG").Range("A76") ActiveCell.Offset(0 + i, 3).Select ActiveCell.FormulaR1C1 = "0%" 'change D76 back to 0% Loop ' go to D77 and repeat actions up until D95 Next i End Sub |
VBA code not working- repost to further explain problem
Thanks a ton! It works--I h ad gotten it to work a different way:
Dim i As Integer For i = 1 To 20 Application.GoTo Sheets("AG").Range("A75") 'this needs to reflect the cell above where the short list starts ActiveCell.Offset(0 + i, 3).FormulaR1C1 = "5%" Sheets("Summary").Range("D3").Offset(0 + i, 0).Value = Sheets("Summary").Range("C4").Value Sheets("Summary").Range("H3").Offset(0 + i, 0).Value = Sheets("Summary").Range("G4").Value Sheets("Summary").Range("P3").Offset(0 + i, 0).Value = Sheets("Summary").Range("O4").Value Sheets("Summary").Range("T3").Offset(0 + i, 0).Value = Sheets("Summary").Range("S4").Value Application.GoTo Sheets("AG").Range("A75") 'this needs to reflect the cell above where the short list starts ActiveCell.Offset(0 + i, 3).Select ActiveCell.FormulaR1C1 = "0%" Next i End Sub But it was sooo slow--this is much faster....I really appreciate it! MDW wrote: OK, I think I understand you better now. So you're always going to get an answer from what the formula in C4 calculates? And you want it to be populated in D5, D6, D7, D8, etc. as you step through the various funds? So let's try this: lngResults = 4 ' Row 4 on the "Summary" tab For R = 76 To 95 ' The function uses the format .Cells(rownumber, columnumber), so 4 would ' correspond to column D Worksheets("AG").Cells(R,4).Value = .05 ' Get the value in C4 vntAnswer = Worksheets("Summary").Range("C4").Value ' Put the answer in the appropriate cell Worksheets("Summary").Cells(lngResults,4).Value = vntAnswer ' 4 - column D ' Set the value of current row back to 0 Worksheets("AG").Cells(R,4).Value = 0 ' Move down a row lngResults = lngResults + 1 Next -- Hmm...they have the Internet on COMPUTERS now! " wrote: this looks like it could work. just a few questions that might help clarify this further. after I add in the Worksheets("AG").Cells(R, 4).Value = 0.05 line, the cell on the summary sheet C4 links to a static cell. The value within the cell, however, is changing as the cell that has the 5% in it changes. Therefore, on the summary sheet, the formula of C4 is static (=AG!CX191: where AGCX191 is calculated from a bunch of other numbers, including the 0% or 5% weight), but the value changes. Therefore, I want to copy, paste special the value into D4, then when the next fund is allocated 5%, that value should be copy pasted from C4 into D5, and so on. Does that make sense? I've been trying to play with it to get that to work, but havent yet figured it out (not very adept at VBA yet). Any help would be greatly appreciated... MDW wrote: First of all, I think you only need one loop to do this. Secondly, if the locations of your inputs and outputs are going to be in known places, you'd probably be better off using the .Cells() function. I don't quite understand the specifics of what you're trying to accomplish, but I'm going to take a stab at it. lngResultsStartingRow = 4 ' Row 4 on your "Summary" tab For R = 76 To 95 ' The function uses the format .Cells(rownumber, columnumber), so 4 would correspond to column D Worksheets("AG").Cells(R,4).Value = .05 ' Do some calculations vntAnswer = Worksheets("AG").Cells(R,5).Value ' 5 = column G ' Take the answer and put it in the appropriate cell of the summary tab Worksheets("Summary").Cells(lngResultsStartingRow, 3).Value = vntAnswer ' 3 = Column C ' Repeat process for other calculations ' Set the value of current row back to 0 Worksheets("AG").Cells(R,4).Value = .05 ' Move down a row on the results tab lngResultsStartingRow = lngResultsStartingRow + 1 Next Hope that gets you in the ballpark. -- Hmm...they have the Internet on COMPUTERS now! " wrote: My macro shoudl work as follows: i have 2 sheets, AG and Summary. On the AG sheet, I have 20 short list funds (starting in cell A76) that I want to add in to the current portfolio at a 5% weighting (which is in column D), and then I calculate the new volatility, return, beta to teh S&P and correlation to the S&P. The Summary sheet links to the AG sheet and to the specific cells that contain these new results, so for example C4 on Summary links to the new volatility of the portfolio with the short list fund, G4 links to the new return, O4 links to the new beta and S4 links to the new correl. Now I want to take these numbers (say C4) and copy paste special the value to D4. Then it shd go back, make the % of the first short list fund equla to 0% and the second fund should now be equal to 5%. THe links are still the same so now I want to copy, pastespecial C4 to D5, and so on. The code below does not loop through (stays on AG.D76, when it shd go to AG.D77 and so on). It also goes to cell C3 in the summary worksheet instaed of C4 and doesnt seem to be doing any pasting. Is this code completely wrong? ANy suggestions? Dim i As Integer For i = 0 To 20 Do Until i = 20 Application.GoTo Sheets("AG").Range("A76") 'this needs to reflect where list starts ActiveCell.Offset(0 + i, 3).Select 'weighting starts in column D76, then goes to D77, etc ActiveCell.FormulaR1C1 = "5%" 'changes weight from 0 to 5% Application.GoTo Sheets("Summary").Range("C4") Selection.Copy 'copy volatility to adjacent cell ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Application.GoTo Sheets("Summary").Range("G4") 'copy return Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("O4") 'copy beta Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("S4") 'copy correlation Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("AG").Range("A76") ActiveCell.Offset(0 + i, 3).Select ActiveCell.FormulaR1C1 = "0%" 'change D76 back to 0% Loop ' go to D77 and repeat actions up until D95 Next i End Sub |
VBA code not working- repost to further explain problem
Glad I could help. :)
-- Hmm...they have the Internet on COMPUTERS now! " wrote: Thanks a ton! It works--I h ad gotten it to work a different way: Dim i As Integer For i = 1 To 20 Application.GoTo Sheets("AG").Range("A75") 'this needs to reflect the cell above where the short list starts ActiveCell.Offset(0 + i, 3).FormulaR1C1 = "5%" Sheets("Summary").Range("D3").Offset(0 + i, 0).Value = Sheets("Summary").Range("C4").Value Sheets("Summary").Range("H3").Offset(0 + i, 0).Value = Sheets("Summary").Range("G4").Value Sheets("Summary").Range("P3").Offset(0 + i, 0).Value = Sheets("Summary").Range("O4").Value Sheets("Summary").Range("T3").Offset(0 + i, 0).Value = Sheets("Summary").Range("S4").Value Application.GoTo Sheets("AG").Range("A75") 'this needs to reflect the cell above where the short list starts ActiveCell.Offset(0 + i, 3).Select ActiveCell.FormulaR1C1 = "0%" Next i End Sub But it was sooo slow--this is much faster....I really appreciate it! MDW wrote: OK, I think I understand you better now. So you're always going to get an answer from what the formula in C4 calculates? And you want it to be populated in D5, D6, D7, D8, etc. as you step through the various funds? So let's try this: lngResults = 4 ' Row 4 on the "Summary" tab For R = 76 To 95 ' The function uses the format .Cells(rownumber, columnumber), so 4 would ' correspond to column D Worksheets("AG").Cells(R,4).Value = .05 ' Get the value in C4 vntAnswer = Worksheets("Summary").Range("C4").Value ' Put the answer in the appropriate cell Worksheets("Summary").Cells(lngResults,4).Value = vntAnswer ' 4 - column D ' Set the value of current row back to 0 Worksheets("AG").Cells(R,4).Value = 0 ' Move down a row lngResults = lngResults + 1 Next -- Hmm...they have the Internet on COMPUTERS now! " wrote: this looks like it could work. just a few questions that might help clarify this further. after I add in the Worksheets("AG").Cells(R, 4).Value = 0.05 line, the cell on the summary sheet C4 links to a static cell. The value within the cell, however, is changing as the cell that has the 5% in it changes. Therefore, on the summary sheet, the formula of C4 is static (=AG!CX191: where AGCX191 is calculated from a bunch of other numbers, including the 0% or 5% weight), but the value changes. Therefore, I want to copy, paste special the value into D4, then when the next fund is allocated 5%, that value should be copy pasted from C4 into D5, and so on. Does that make sense? I've been trying to play with it to get that to work, but havent yet figured it out (not very adept at VBA yet). Any help would be greatly appreciated... MDW wrote: First of all, I think you only need one loop to do this. Secondly, if the locations of your inputs and outputs are going to be in known places, you'd probably be better off using the .Cells() function. I don't quite understand the specifics of what you're trying to accomplish, but I'm going to take a stab at it. lngResultsStartingRow = 4 ' Row 4 on your "Summary" tab For R = 76 To 95 ' The function uses the format .Cells(rownumber, columnumber), so 4 would correspond to column D Worksheets("AG").Cells(R,4).Value = .05 ' Do some calculations vntAnswer = Worksheets("AG").Cells(R,5).Value ' 5 = column G ' Take the answer and put it in the appropriate cell of the summary tab Worksheets("Summary").Cells(lngResultsStartingRow, 3).Value = vntAnswer ' 3 = Column C ' Repeat process for other calculations ' Set the value of current row back to 0 Worksheets("AG").Cells(R,4).Value = .05 ' Move down a row on the results tab lngResultsStartingRow = lngResultsStartingRow + 1 Next Hope that gets you in the ballpark. -- Hmm...they have the Internet on COMPUTERS now! " wrote: My macro shoudl work as follows: i have 2 sheets, AG and Summary. On the AG sheet, I have 20 short list funds (starting in cell A76) that I want to add in to the current portfolio at a 5% weighting (which is in column D), and then I calculate the new volatility, return, beta to teh S&P and correlation to the S&P. The Summary sheet links to the AG sheet and to the specific cells that contain these new results, so for example C4 on Summary links to the new volatility of the portfolio with the short list fund, G4 links to the new return, O4 links to the new beta and S4 links to the new correl. Now I want to take these numbers (say C4) and copy paste special the value to D4. Then it shd go back, make the % of the first short list fund equla to 0% and the second fund should now be equal to 5%. THe links are still the same so now I want to copy, pastespecial C4 to D5, and so on. The code below does not loop through (stays on AG.D76, when it shd go to AG.D77 and so on). It also goes to cell C3 in the summary worksheet instaed of C4 and doesnt seem to be doing any pasting. Is this code completely wrong? ANy suggestions? Dim i As Integer For i = 0 To 20 Do Until i = 20 Application.GoTo Sheets("AG").Range("A76") 'this needs to reflect where list starts ActiveCell.Offset(0 + i, 3).Select 'weighting starts in column D76, then goes to D77, etc ActiveCell.FormulaR1C1 = "5%" 'changes weight from 0 to 5% Application.GoTo Sheets("Summary").Range("C4") Selection.Copy 'copy volatility to adjacent cell ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone Application.GoTo Sheets("Summary").Range("G4") 'copy return Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("O4") 'copy beta Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("Summary").Range("S4") 'copy correlation Selection.Copy ActiveCell(0 + i, 1).Select Selection.PasteSpecial Paste:=xlPasteValues Application.GoTo Sheets("AG").Range("A76") ActiveCell.Offset(0 + i, 3).Select ActiveCell.FormulaR1C1 = "0%" 'change D76 back to 0% Loop ' go to D77 and repeat actions up until D95 Next i End Sub |
| All times are GMT +1. The time now is 03:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com