jeudi 6 août 2015

Adding counter to ElseIF statement

I have the following code that checks four different conditions, and gives me the cumulative value for the cells that meet each condition. I would like to add a counter that only counts the cells that meet the specific condition. Below, I have market that counter with k. For example, if UpDown=1, I want the counter to count only the number of cells that meet that condition. Currently, my code does not work that way. Any help would be appreciated!

Public Function Test(UpDown As Double, rng_port As Range, rng_bench As Range) As Double

Dim l As Long, temp As Double, k as integer

k=0

If rng_port.Count <> rng_bench.Count Then upcaptureratio = xlErrValue
cumret_port = 1
For l = 1 To rng_bench.Count

    If UpDown = 1 Then
        temp = 1 + IIf(rng_bench.Cells(l) >= 0.0025, rng_port.Cells(l), 0)

     k=k+1

    ElseIf UpDown = 2 Then
        temp = 1 + IIf(rng_bench.Cells(l) < 0.0025 And rng_bench.Cells(l) >= 0, rng_port.Cells(l), 0)

     k=k+1

    ElseIf UpDown = 3 Then
        temp = 1 + IIf(rng_bench.Cells(l) < 0 And rng_bench.Cells(l) >= -0.0025, rng_port.Cells(l), 0)


        k=k+1

        ElseIf UpDown = 4 Then
        temp = 1 + IIf(rng_bench.Cells(l) < -0.0025, rng_port.Cells(l), 0)

k=k+1

    End If

        cumret_port = cumret_port * temp

Next

Test = cumret_port^(1/k) - 1

Aucun commentaire:

Enregistrer un commentaire