none
VBA RRS feed

  • Question

  • 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 integer, 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

    End Function

    I also tried inserting the following function within each ElseIf block: k=k+Application.WorksheetFunction.CountIf(ring_bench,"<-0.0025") - the condition value varies depending on what scenario I want to test -  but it does not appear to give me the right results. For example, if UpDown=4, and there are only 3 observations meeting that criteria, then Test (my output variable) should equal to cumret_port^(1/3)-1 but it does not appear to work. 

    Thank you

    Saturday, August 8, 2015 11:51 AM

All replies

  • Hi new987,

    This forum is to ask questions and comment about developing apps for Office 2013. Your issue is more related with vba code, I will move this thread to the more related forum.

    Reference: http://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    Thanks for your understanding.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, August 10, 2015 2:20 AM
    Moderator
  • Re:  code to count or calculate or ?

    Its not clear to me what is needed, maybe this gets you closer...
    '---
    Public Function Test(UpDown As Integer, 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 rng_bench(L).Value = UpDown Then
        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
      End If
      cumret_port = cumret_port * temp
     Next
     Test = cumret_port ^ (1 / k) - 1
    End Function
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 11:53 PM
    Monday, August 10, 2015 5:23 AM