# UDF Array Help

#### VBE313

##### Well-known Member
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``````

### 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
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

#### VBE313

##### Well-known Member
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
Do you have any "Y" in the second range?

#### VBE313

##### Well-known Member
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

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

Replies
6
Views
179
Replies
5
Views
31
Replies
13
Views
220
Replies
2
Views
73
Replies
12
Views
199