Looping issues

[jonc31]

Can someone explain or show me some sample code that stores computed information in a loop as a variable?

Basicall I have to calculate the assests in certain strategies. Each strategy can have certain funds/accounts in them. (Look Below)


Strategy Fund %Invested AUM
Long Alpha 100.00% 5
Long Beta 100.00% 10
Long Gamma 100.00% 20
Short ABC 100.00% 12
Short Alpha 20.00% 5

Straegy = column A
Fund = column B
%invested = column c

So above you have two different strategies Long and Short. The same fund can be invested in two different strategies. I want to create a loop that takes every line it finds the word long in column A it multiplies the %Invested x the AUM( first line would be 100*5) and stores that answer as and then goes to the second line, does the same thing and adds it to the orignal computation. The mathmatical expression for lop two would be ( Answer from loop one + (100%*10)). This process would continue until line three because line 4 is a different strategy. Before it is done with computting all the AUM in the long strategy i want to store the sum of the three loops in a variable.

Anyone have some code that can help me out?

 

[rdw72777]

Jonc31,

I'll call this a poor man's loop. It basically defines the columns and rows, loops through and does a single calculation and cumulative calculation for each row, and when its done summing a strategy it pops up a message box.

I call it poor man's because there are several gaps:
1. It assumes the data is sorted so that all Longs will appear together and all shorts will appear together, as in your sample data. if they are mixed some mods might be needed.
2. There are a bunch of If formulas to handle the first and last rows of data the way I did it. Better coding could remove this but I'm a "get-it-done" type coder.

Let me know if this works or if there are scenarios it fails at, I could try and resolve if needed.

Sub Process_Report()

Dim wkb As Workbook
Dim Sheet1 As Worksheet
Dim StrategyCol As Variant, FundCol As Variant, PctInvestedCol As Long, AUMCol As Long
Dim Sheet1LastRow As Long
Dim myStrategy As Variant, myFund As Variant, myPctInvested As Single, myAUM As Long
Dim myCumValue As Long, myInterimValue As Long
Dim myPrevStrategy As Variant

Set wkb = ThisWorkbook
Set Sheet1 = wkb.Worksheets("Sheet5")

'=============Define Variables=================================================================
    '======= Sheet1 ==================================
    With Sheet1.Rows(1)
    
        StrategyCol = .Find(What:="Strategy", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
        FundCol = .Find(What:="Fund %", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
        PctInvestedCol = .Find(What:="Invested", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
        AUMCol = .Find(What:="AUM", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
        Sheet1LastRow = .Cells(Rows.Count, StrategyCol).End(xlUp).Row
        
    End With
    
    '============Loop through each sheet, set values on 4th sheet ==============================
    'Set myCumValue to 0 before doing loop for Cumulative value
    myCumValue = 0
    
    For i = 2 To Sheet1LastRow
    
        With Sheet1
        
            'Set prev Strategy to be nothing if its the first row
            If i = 2 Then
                myPrevStrategy = ""
            End If
            
            'Set variables
            myStrategy = .Cells(i, StrategyCol).Value
            myFund = .Cells(i, FundCol).Value
            myPctInvested = .Cells(i, PctInvestedCol).Value
            myAUM = .Cells(i, AUMCol).Value
            
            If myPrevStrategy <> myStrategy And myPrevStrategy <> "" Then
                MsgBox myPrevStrategy & vbLf & myCumValue
                myCumValue = 0
            End If
            
            myInterimValue = myPctInvested * myAUM
            myCumValue = myCumValue + myInterimValue
            myPrevStrategy = myStrategy
            
            If i = Sheet1LastRow Then
                MsgBox myStrategy & vbLf & myCumValue
            End If
        
        End With
        
    Next i
            
End Sub

 

[jonc31]

The code breaks on the line below:

FundCol = .Find(What:="Fund %", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column

 

[rdw72777]

Oh, I see, the column header was Fund and then the next was % invested.

Put these 2 rows of code in place of the old ones.

FundCol = .Find(What:="Fund", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column
PctInvestedCol = .Find(What:="%Invested", SearchDirection:=xlPrevious, LookAt:=xlWhole, SearchOrder:=xlByColumns).Column