Help with UDF Array

[VBE313]

in the following code, I am trying to Extract just the "Y" numbers. My code gives me the answer I need, but the array UDF uses 2 columns. I only want my answer to be in column E not column F. What am I doing wrong?

Option Explicit
Function getY(rng As Range)
Dim i As Integer, j As Integer, n As Integer
Dim temp As Integer, A()
n = rng.Rows.Count
ReDim A(n, 1)
A = rng
    For j = 1 To n
        If A(j, 2) = "Y" Then
            temp = A(j, 1)
            A(j, 2) = ""
        Else
            A(j, 1) = ""
            A(j, 2) = ""
        End If
    Next j
getY = A
End Function

 

[Fluff]

If you want to stick to VBA, how about

Function getY(Rng As Range) As Variant
   Dim j As Long, n As Long
   Dim A As Variant, B As Variant
   n = Rng.Rows.Count
   ReDim B(1 To n, 1 To 1)
   A = Rng.Value2
   For j = 1 To n
      If A(j, 2) = "Y" Then
         B(j, 1) = A(j, 1)
      Else
         B(j, 1) = ""
      End If
   Next j
   getY = B
End Function

 

[VBE313]

Here is another function that you could try

Function getY(rng As Range)
  getY = Evaluate(Replace(Replace("if(#=""Y"",^,"""")", "#", rng.Columns(2).Address), "^", rng.Columns(1).Address))
End Function

I'm assuming due to

thank you!

 

[Peter_SSs]

thank you!

You're welcome.