Excel VBA Function to Loop from Cell upto top of Range

[hazza147]

Hi Guys,

Looking to create a function which allows you to select a cell and then run a For loop against each cell inside the range from the selected cell to the top of the column.

I.e. if I select cell C5 it will run a for loop against C5, C4, C3, C2, C1 in that order. The formula calculates an expodentially weighted value upto that point in time using a half life of 0.5.

So, if below represent C1:C5:

100
100
100
100
200

It would calculate 200*0.5 + 100 * 0.25 + 100 * 0.125...

Below is my current code:

Function HALF_LIFE(cell As Range, half As Double)

    Dim i As Long
    Dim rng As Range
    
    cell2 = cell.Address
    
    rng = Range(cell2, Range(cell2).End(xlUp)) ' Gets the whole range
    
    NumRows = rng.Rows.Count
    
    For i = NumRows To 1 Step -1
        ' Formula to calculate weighted amount
    Next i




End Function

Cannot get it to work even though debug shows that cell2 is equal to $C$5.

Any suggestions would be great.

 

[jmacleary]

Hi there. Assuming that you always want to go from the cell passed in up to C1, then this amended code should work:

Function HALF_LIFE(cellin As Range, half As Double)

    Dim i As Long

    
    NumRows = cellin.Row
    
    For i = NumRows To 1 Step -1
        ' Formula to calculate weighted amount
    Next i




End Function

PS I have changed cell to cellin in the parameter area as it is always a bit risky using VBA reserved words like cell for your variable names.

 

[steve the fish]

How about doing it with the range you pass to the function being the range you want to use eg:

Function HALF_LIFE(values As Range, half As Double)

Dim i As Long, arr

If values.Columns.Count > 1 Then Exit Function

arr = values

For i = UBound(arr, 1) To LBound(arr, 1) Step -1
    If IsNumeric(arr(i, 1)) Then
        HALF_LIFE = HALF_LIFE + (arr(i, 1) * half)
        half = half / 2
    End If
Next

End Function

 

[hazza147]

This works well. Thanks Steve.

How about doing it with the range you pass to the function being the range you want to use eg:

Function HALF_LIFE(values As Range, half As Double)

Dim i As Long, arr

If values.Columns.Count > 1 Then Exit Function

arr = values

For i = UBound(arr, 1) To LBound(arr, 1) Step -1
    If IsNumeric(arr(i, 1)) Then
        HALF_LIFE = HALF_LIFE + (arr(i, 1) * half)
        half = half / 2
    End If
Next

End Function