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

 

[Legacy 456155]

Replace
ReDim A(n, 1)
with
ReDim A(n, 1 To 1) OR ReDim A(n, 0)

 

[VBE313]

Replace
ReDim A(n, 1)
with
ReDim A(n, 1 To 1) OR ReDim A(n, 0)

Hi, thank you but I was not successful with this. What else can be wrong? here is a new code

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, 0)
A = rng
    For j = 1 To n
        If A(j, 2) = "Y" Then
            i = 0
            temp = A(j, 1)
            A(j, 2) = ""
        Else
            i = i + 1
            temp = A(j - i, 1)
            A(j - i, 1) = temp + A(j, 1)
            A(j, 1) = ""
            A(j, 2) = ""
        End If
    Next j
getY = A
End Function

 

[Legacy 456155]

I don't know. I would have to create an example to work with. If nobody helps you out by the time I get back tomorrow or you haven't figured it out by then on your own, I'll see if I can help you. Have a good one!

 

[Legacy 456155]

I tried your first example and edited it to return an array with the correct sized dimensions. Your current function returns the same dimensions as the range passed as an argument. 2 columns. So I added an array.

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

This is a long way around the block to get the results you are looking for. I suppose that you are doing this for your own learning? If not, there are easier ways to get your Y's.

 

[VBE313]

I tried your first example and edited it to return an array with the correct sized dimensions. Your current function returns the same dimensions as the range passed as an argument. 2 columns. So I added an array.

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

This is a long way around the block to get the results you are looking for. I suppose that you are doing this for your own learning? If not, there are easier ways to get your Y's.

Exactly! I am doing this for my own learning! Thank you very much this worked perfectly! I loved how you used ret(). Didnt think to also create an empty array to fill the answers with. I realized that they need to be Empty instead of "".

 

[VBE313]

what does the 0 do here?

ReDim ret(1 To n, 0)

 

[Legacy 456155]

A range assigned to a variant or variant array always results in a two dimensional array. You know, v(Rows, Columns). The zero in the second dimension means a single column in a zero based array which is the default. Frankly, when working with ranges the semantics are a bit cleaner IMO using ReDim ret(1 To n, 1 To 1). I just use zero as a habit because most languages only allow zero based arrays.

So the following should be equivalent, but maybe a bit more clear when working with Excel ranges.

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

 

[Jon von der Heyden]

I'm curious, why not use a regular formula approach, e.g.:

=IF(B1:B9="Y",A1:A9,"")

Which I expect will work with Excel versions supporting dynamic arrays (which I suspect yours does). My version does not currently.

Alternatively, you could also probably limit it to one column using INDEX, e.g:

=INDEX(getY(A1:B9),0,1)

 

[Peter_SSs]

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 curious, why not use a regular formula approach

I'm assuming due to

I suppose that you are doing this for your own learning?

Exactly! I am doing this for my own learning!