UDF Array Help

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
664
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,256
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Well-known Member
Joined
Mar 22, 2019
Messages
664
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,256
Office Version
  1. 365
Platform
  1. Windows
Do you have any "Y" in the second range?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,256
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,114,278
Messages
5,546,939
Members
410,764
Latest member
Dedeke
Top