problems iwth VBA code- for each loop
 

When I run the following code, I get the error "For loop not
initialized". Im having problems iwth the code enclosed by asterisks.
What im trying to do is to make the weight for each fund below row 79
go from 0% to 5% then back to 0% before moving on to the next fund (the
font for these funds below row 79 is red. When the fund is at a 5%
weight, I want to see waht strategy it is (which will be in column B)
and find all the funds above row 80 that match that strategy and
perform a calculation on those funds.
the spreadsheet looks something like this
Column A Column B Column D
Column E
Row 1 Fund A 3.20% Conv
Arb
Row 2 Fund B 6.20% Conv
Arb
Row 79 Fund C 4.10% Equity
LS
Row 80 Short list A Conv Arb 5%
Row 81 Short list B Equity LS 0%

So In this case, SHort list A is at the 5% weight, and is a conv arb
fund. So I want to take the 3.2%/((9.4%)/(4.4%)) where 9.4% is the
3.2%+6.2% for the conv arb funds and 4.4% is the 9.4%-5%



Dim CellW As Range
Dim CellY As Range


Dim i As Integer
Number = CountRed(Worksheets("AG").Range("A1:A110"))
For i = 1 To Number
Application.Goto Sheets("AG").Range("A79") 'this needs to
reflect the cell above where the short list starts
ActiveCell.Offset(0 + i, 3).FormulaR1C1 = "5%"
For Each CellW In Worksheets("ag").Range("D4:D79") ' the
ending cell needs to be the same as range above
**** If ActiveCell.Offset(0 + i, 3).Value = "5%" _
And ActiveCell.Offset(0 + i, 3).Font.ColorIndex = 3 _
And Range("E:E").Text = ActiveCell.Offset(0 + i, 1).Text
Then
CellW.Value = (Range("E:E").Offset(0, -1).Value /
((Application.Sum(Range("E:E")).Offset(0, -1)) /
(Application.Sum(Range("E:E")).Offset(0, -1)) - 0.05))****
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("A79") 'this needs to
reflect the cell above where the short list starts
ActiveCell.Offset(0 + i, 3).Select
ActiveCell.FormulaR1C1 = "0%"
End If
Next CellW
Next i

End Sub

Any suggestions?

problems iwth VBA code- for each loop
 

did you work this out or you still need help with this?
wrote:
When I run the following code, I get the error "For loop not
initialized". Im having problems iwth the code enclosed by asterisks.
What im trying to do is to make the weight for each fund below row 79
go from 0% to 5% then back to 0% before moving on to the next fund (the
font for these funds below row 79 is red. When the fund is at a 5%
weight, I want to see waht strategy it is (which will be in column B)
and find all the funds above row 80 that match that strategy and
perform a calculation on those funds.
the spreadsheet looks something like this
Column A Column B Column D
Column E
Row 1 Fund A 3.20% Conv
Arb
Row 2 Fund B 6.20% Conv
Arb
Row 79 Fund C 4.10% Equity
LS
Row 80 Short list A Conv Arb 5%
Row 81 Short list B Equity LS 0%

So In this case, SHort list A is at the 5% weight, and is a conv arb
fund. So I want to take the 3.2%/((9.4%)/(4.4%)) where 9.4% is the
3.2%+6.2% for the conv arb funds and 4.4% is the 9.4%-5%



Dim CellW As Range
Dim CellY As Range


Dim i As Integer
Number = CountRed(Worksheets("AG").Range("A1:A110"))
For i = 1 To Number
Application.Goto Sheets("AG").Range("A79") 'this needs to
reflect the cell above where the short list starts
ActiveCell.Offset(0 + i, 3).FormulaR1C1 = "5%"
For Each CellW In Worksheets("ag").Range("D4:D79") ' the
ending cell needs to be the same as range above
**** If ActiveCell.Offset(0 + i, 3).Value = "5%" _
And ActiveCell.Offset(0 + i, 3).Font.ColorIndex = 3 _
And Range("E:E").Text = ActiveCell.Offset(0 + i, 1).Text
Then
CellW.Value = (Range("E:E").Offset(0, -1).Value /
((Application.Sum(Range("E:E")).Offset(0, -1)) /
(Application.Sum(Range("E:E")).Offset(0, -1)) - 0.05))****
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("A79") 'this needs to
reflect the cell above where the short list starts
ActiveCell.Offset(0 + i, 3).Select
ActiveCell.FormulaR1C1 = "0%"
End If
Next CellW
Next i

End Sub

Any suggestions?

problems iwth VBA code- for each loop
 

Before the code If ActiveCell.Offset(0 + i, 3).Value = "5%"
try inserting ActiveCell.Offset(0 + i, 3).select and then change to If
ActiveCell.value="5%"


wrote:
When I run the following code, I get the error "For loop not
initialized". Im having problems iwth the code enclosed by asterisks.
What im trying to do is to make the weight for each fund below row 79
go from 0% to 5% then back to 0% before moving on to the next fund (the
font for these funds below row 79 is red. When the fund is at a 5%
weight, I want to see waht strategy it is (which will be in column B)
and find all the funds above row 80 that match that strategy and
perform a calculation on those funds.
the spreadsheet looks something like this
Column A Column B Column D
Column E
Row 1 Fund A 3.20% Conv
Arb
Row 2 Fund B 6.20% Conv
Arb
Row 79 Fund C 4.10% Equity
LS
Row 80 Short list A Conv Arb 5%
Row 81 Short list B Equity LS 0%

So In this case, SHort list A is at the 5% weight, and is a conv arb
fund. So I want to take the 3.2%/((9.4%)/(4.4%)) where 9.4% is the
3.2%+6.2% for the conv arb funds and 4.4% is the 9.4%-5%



Dim CellW As Range
Dim CellY As Range


Dim i As Integer
Number = CountRed(Worksheets("AG").Range("A1:A110"))
For i = 1 To Number
Application.Goto Sheets("AG").Range("A79") 'this needs to
reflect the cell above where the short list starts
ActiveCell.Offset(0 + i, 3).FormulaR1C1 = "5%"
For Each CellW In Worksheets("ag").Range("D4:D79") ' the
ending cell needs to be the same as range above
**** If ActiveCell.Offset(0 + i, 3).Value = "5%" _
And ActiveCell.Offset(0 + i, 3).Font.ColorIndex = 3 _
And Range("E:E").Text = ActiveCell.Offset(0 + i, 1).Text
Then
CellW.Value = (Range("E:E").Offset(0, -1).Value /
((Application.Sum(Range("E:E")).Offset(0, -1)) /
(Application.Sum(Range("E:E")).Offset(0, -1)) - 0.05))****
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("A79") 'this needs to
reflect the cell above where the short list starts
ActiveCell.Offset(0 + i, 3).Select
ActiveCell.FormulaR1C1 = "0%"
End If
Next CellW
Next i

End Sub

Any suggestions?

problems iwth VBA code- for each loop
 

have not worked it out yet...tried the suggestion below, but that gave
me a type mismatch...let me try putting in the code again so that it's
easier to read:

What im trying to do is to make the weight for each fund below row 79
go from 0% to 5% then back to 0% before moving on to the next fund (the

font for these funds below row 79 is red). When the fund is at a 5%
weight, I want to see what strategy it is (which will be in column B)
and find all the funds above row 80 that match that strategy and
perform a calculation on those funds.
the spreadsheet looks something like this

_______Column A____ Column B_______ Column D________ Column E
Row 1 ___Fund A _____________________3.20% __________Conv Arb
Row 2 ___Fund B______________________6.20%__________ Conv Arb
Row 79___Fund C _____________________4.10% __________Equity LS
Row 80_Short list A ____Conv Arb________ 5%
Row 81_Short list B____ Equity LS________ 0%



Dim i As Integer
Number = CountRed(Worksheets("AG").Range("A1:A110"))
For i = 1 To Number
Application.Goto Sheets("AG").Range("A79") 'this needs to reflect
the cell above where the short list starts
ActiveCell.Offset(0 + i, 3).FormulaR1C1 = "5%"
*** For Each CellW In Worksheets("ag").Range("D4:D79") ' the ending
cell needs to be the same as range above
If ActiveCell.Offset(0 + i, 3).Value = "5%" _
And CellW.Offset(0, 1).Text = ActiveCell.Offset(0 + i, 1).Text Then
CellW.Value = (Range("D:D").Value) / (Application.Sum(Range("D:D")) /
(Application.Sum(Range("D:D")) - 0.05))***
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("A79") 'this needs to reflect the
cell above where the short list starts
ActiveCell.Offset(0 + i, 3).Select
ActiveCell.FormulaR1C1 = "0%"
End If
Next CellW
Next i

End Sub


stevebriz wrote:
did you work this out or you still need help with this?
wrote:
When I run the following code, I get the error "For loop not
initialized". Im having problems iwth the code enclosed by asterisks.
What im trying to do is to make the weight for each fund below row 79
go from 0% to 5% then back to 0% before moving on to the next fund (the
font for these funds below row 79 is red. When the fund is at a 5%
weight, I want to see waht strategy it is (which will be in column B)
and find all the funds above row 80 that match that strategy and
perform a calculation on those funds.
the spreadsheet looks something like this
Column A Column B Column D
Column E
Row 1 Fund A 3.20% Conv
Arb
Row 2 Fund B 6.20% Conv
Arb
Row 79 Fund C 4.10% Equity
LS
Row 80 Short list A Conv Arb 5%
Row 81 Short list B Equity LS 0%

So In this case, SHort list A is at the 5% weight, and is a conv arb
fund. So I want to take the 3.2%/((9.4%)/(4.4%)) where 9.4% is the
3.2%+6.2% for the conv arb funds and 4.4% is the 9.4%-5%



Dim CellW As Range
Dim CellY As Range


Dim i As Integer
Number = CountRed(Worksheets("AG").Range("A1:A110"))
For i = 1 To Number
Application.Goto Sheets("AG").Range("A79") 'this needs to
reflect the cell above where the short list starts
ActiveCell.Offset(0 + i, 3).FormulaR1C1 = "5%"
For Each CellW In Worksheets("ag").Range("D4:D79") ' the
ending cell needs to be the same as range above
**** If ActiveCell.Offset(0 + i, 3).Value = "5%" _
And ActiveCell.Offset(0 + i, 3).Font.ColorIndex = 3 _
And Range("E:E").Text = ActiveCell.Offset(0 + i, 1).Text
Then
CellW.Value = (Range("E:E").Offset(0, -1).Value /
((Application.Sum(Range("E:E")).Offset(0, -1)) /
(Application.Sum(Range("E:E")).Offset(0, -1)) - 0.05))****
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("A79") 'this needs to
reflect the cell above where the short list starts
ActiveCell.Offset(0 + i, 3).Select
ActiveCell.FormulaR1C1 = "0%"
End If
Next CellW
Next i

End Sub

Any suggestions?