How to loop through an array from XLookup to return a specific value in VBA?

Question

I'm working on a custom function and so far I was able to run a Xlookup within it in VBA. Now I want to return a specific array value from the xlookup based on certain criteria. I'm stuck on how to actually do this.

The condition I want is if the array value does not equal 0, it will return that value. Otherwise, it will keep checking the other values.

Here is what I have so far. It also included my various attempts of trying to do it.

Function SWFQReport(sales_order_lookup, sales_order_column, order_dates)
    date_array_lookup = Application.XLookup(sales_order_lookup, sales_order_column, order_dates, "NONE", 0)
    date_array = Array(date_array_lookup)

    'Dim order_date As String'
    'Dim i As Interger'
    'For i = 2 To 3'
        'If date_array(i) <> 0 Then order_date = i '
        'Else: order_date = 0'
    
    'Dim order_date As Variant'
    'Debug.Print date_array_lookup'
    
    'If date_array(1) <> 0 Then'
        'order_date = date_array(1)'
    'Else'
        'order_date = "TEST"'
    'End If'

    SWFQReport = date_array
End Function

Answer 1

Allen, there were a number of issues in your code. You did not provide an example of your data but based on your follow-on response, I took a best guess and created some sample data as follows:

I then reworked the code. The line date_array = Array(date_array_lookup) was redundant because date_array_lookup is already a multi-dimensional array. Converting it again only confused the issue. SWFQReport should be declared as a variant because it can return either a date or string value. You will also need to tweak the For ... Next loop. The new function starts at column 1 instead of column 2 which you originally had:

Function SWFQReport(sales_order_lookup, sales_order_column, order_dates) As Variant

    Dim i As Integer

    On Error Resume Next

    SWFQReport = 0

    date_array_lookup = Application.XLookup(sales_order_lookup, sales_order_column, order_dates, "NONE", 0)

    ' Test if we found anything. If not, Ubound will throw an error
    ' because the returned data is a variant string (Value=NONE) and not an array.
    If UBound(date_array_lookup) = 0 Then
        SWFQReport = "NONE"
        Exit Function
    End If

    ' Find first non-zero date and return that
    For i = 1 To 3
        If date_array_lookup(1, i) <> 0 Then
            SWFQReport = date_array_lookup(1, i)
            Exit For
        End If
    Next i

End Function

Based on my sample data, I created another function to test the result:

Function fTest()

    Cells(1, 6) = SWFQReport("A0002", Range("A1:A5"), Range("B1:D5"))
    
End Function

Set this up in a test workbook and have a play with it. To see what is going on, in the VB editor position your cursor inside the fTest function and press F8 to begin stepping through the code. You should also open the Locals Window (View -> Locals Window) which will let you inspect the contents of all variables.