- Mar 22, 2019
- Office Version
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