UDF Array Help

[VBE313]

In the following function, I want to get every column that says "Y" and sum everything until the next "Y". My function works properly. However, When there is a "N" at the beginnning, it will give a #VALUE! Which makes sense because my logic says if it doesn't = Y, then i = i + 1 and then ret(j - i, 0). This obviously will not work effectively if "N" is the first cell. How can I add to this logic so It will still work when "N" is the first cell?

Function getY(Rng1 As Range, Rng2 As Range) As Variant
    Dim i As Integer, j As Integer, n As Integer
    Dim A As Variant, B As Variant, ret()
    n = Rng1.Rows.Count
    ReDim ret(1 To n, 0)
    A = Rng1.Value2
    B = Rng2.Value2
    For j = 1 To n
        If B(j, 1) = "Y" Then
            ret(j, 0) = A(j, 1)
            i = 0
        Else
            i = i + 1
            ret(j - i, 0) = ret(j - i, 0) + A(j, 1)
        End If
    Next j
    getY = ret
End Function

 

[Fluff]

How about

Function getY(Rng1 As Range, Rng2 As Range) As Variant
    Dim i As Integer, j As Integer, n As Integer
    Dim A As Variant, B As Variant, ret()
    Dim Flg As Boolean
    n = Rng1.Rows.Count
    ReDim ret(1 To n, 0)
    A = Rng1.Value2
    B = Rng2.Value2
    For j = 1 To n
        If B(j, 1) = "Y" Then
         Flg = True
            ret(j, 0) = A(j, 1)
            i = 0
        ElseIf Flg Then
            i = i + 1
            ret(j - i, 0) = ret(j - i, 0) + A(j, 1)
        End If
    Next j
    getY = ret
End Function

 

[VBE313]

How about

Function getY(Rng1 As Range, Rng2 As Range) As Variant
    Dim i As Integer, j As Integer, n As Integer
    Dim A As Variant, B As Variant, ret()
    Dim Flg As Boolean
    n = Rng1.Rows.Count
    ReDim ret(1 To n, 0)
    A = Rng1.Value2
    B = Rng2.Value2
    For j = 1 To n
        If B(j, 1) = "Y" Then
         Flg = True
            ret(j, 0) = A(j, 1)
            i = 0
        ElseIf Flg Then
            i = i + 1
            ret(j - i, 0) = ret(j - i, 0) + A(j, 1)
        End If
    Next j
    getY = ret
End Function

Hi Fluff! Thank you for the help! It gives me 0 all the way down

 

[Fluff]

Do you have any "Y" in the second range?

 

[VBE313]

Do you have any "Y" in the second range?

im sorry it did work! I had "Yes" instead of "Y". Sorry for that. Thank you very much!!

 

[Fluff]

You're welcome & thanks for the feedback