VBA code not working- repost to further explain problem

[jsuden]

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

 

[Gary''s Student]

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.

 

[MDW]

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.

 

[MDW]

Sorry, it should be:

' Set the value of current row back to 0
Worksheets("AG").Cells(R,4).Value = 0

 

[jsuden]

ahhh, did not know that...very helpful. Thx!@

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.

 

[jsuden]

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]

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

 

[jsuden]

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]

Glad I could help.