UDF returning an array and array formula

[crookesa]

I have written a UDF which returns an array and want to display all of those values using an array formula on my sheet.

The UDF is:

Function GetDutyDetails(Rng As Range) As Variant


Dim ArraySize As Integer
ArraySize = Worksheets("ATCO").Range("atco_non_operational_duties").Rows.count


Dim DirArray() As Variant
ReDim DirArray(ArraySize)
DirArray = Worksheets("ATCO").Range("atco_non_operational_duties").Value


Dim Duties() As Variant
Dim i As Integer: i = 0
ReDim Duties(i)


For Each cell In Rng
    'Debug.Print cell.Value
    If IsInArray(cell.Value, DirArray) Then
        'Debug.Print ("Cell value - " & cell.Value)
    Else
        'Debug.Print ("== Operational Duty (" & cell.Value & ") == ")
        If IdentifyDutyType(cell.Value) = "Half AL" Then
            Debug.Print ("** Half AL **")
            Duties(i) = ("** Half AL **")
            i = i + 1
            ReDim Preserve Duties(i)
        ElseIf IdentifyDutyType(cell.Value) = "Medical" Then
            Debug.Print ("** Medical **")
            Duties(i) = ("** Medical **")
            i = i + 1
            ReDim Preserve Duties(i)
        ElseIf IdentifyDutyType(cell.Value) = "Early Arrival" Then
            Debug.Print ("** Early Arrival **")
            Duties(i) = ("** Early Arrival **")
            i = i + 1
            ReDim Preserve Duties(i)
        ElseIf IdentifyDutyType(cell.Value) = "Duty Extension" Then
            Debug.Print ("** Duty Extension **")
            Duties(i) = ("** Duty Extension **")
            i = i + 1
            ReDim Preserve Duties(i)
        ElseIf IdentifyDutyType(cell.Value) = "Ad Hoc Duty" Then
            Debug.Print ("** Ad Hoc Duty **")
            Duties(i) = ("** Ad Hoc Duty **")
            i = i + 1
            ReDim Preserve Duties(i)
        End If
      
    End If


Next cell


ReDim Preserve Duties(i - 1)


GetDutyDetails = Duties()


End Function

My immediate window for the supplied range is below which is as expected and correct

** Early Arrival **
** Ad Hoc Duty **

The formula I am using is an array formula and I have also tried it as a normal formula but am not getting the results as expected

=GetDutyDetails(ATCO!$K$16:ATCO!$K$20)

The cells where the formula is are just showing the same value (i.e. first in the array) over and over again.

** Early Arrival **
** Early Arrival **
** Early Arrival **
** Early Arrival **
** Early Arrival **
** Early Arrival **
** Early Arrival **

Does anyone know where might be going wrong or how to get the expected output on the sheet from the formula I have written.

 

[NdNoviceHlp]

ArraySize = Worksheets("ATCO").Range("atco_non_operational_duties").Rows.count -1
Dim i As Integer: i = 1

Trial fixing these parts first. HTH. Dave

 

[NdNoviceHlp]

Whoops that's not right on 2nd read. This part here seems wrong though...

Dim i As Integer: i = 0
ReDim Duties(i)
For Each cell In Rng
    'Debug.Print cell.Value
    If IsInArray(cell.Value, DirArray) Then
        'Debug.Print ("Cell value - " & cell.Value)
    Else
        'Debug.Print ("== Operational Duty (" & cell.Value & ") == ")
        If IdentifyDutyType(cell.Value) = "Half AL" Then
            Debug.Print ("** Half AL **")
            Duties(i) = ("** Half AL **")

This part here...

Dim i As Integer: i = 0
ReDim Duties(i)

gives no positions to the array which U then assign a value to it..

 Duties(i) = ("** Half AL **")

So...

Dim i As Integer: i = 1

Or...

   i = i + 1
            ReDim Preserve Duties(i)
            Duties(i) = ("** Half AL **")

Same change for all. HTH. Dave

 

[crookesa]

So I changed the following line

[COLOR=#333333]Dim i As Integer: i = 1[/COLOR]

and my cell with the formula shows 0 now for all the cells. If I show you my Immediate and locals window after your change it looks like this

I see that the first value is empty and presume that's why it's returning zero.
If I revert back to my original code and look at the same windows again I see what I'm expecting to see in the values of the Duties() array.

My problem is still that when I execute the formula in a cell I get the first value from the array repeat down the cells whether I use a formula array (I presume I should use a formula array?) or not.

 

[crookesa]

I'm just getting to trying to find a solution for this one.
Has anyone as suggestions as to how to display what the function is returning to the Immediate window?

As in I want the Duties Array values to be returned to the cells in the spreadsheet, if that is possible.