VBA Array XLookup

[Rex2024]

I am converting a large Macro to array format and need to convert the Sum & Xlookup functions to the equivalent. My issue is I need to XLookup across a row of columns, not a series of rows.

wsData.Range("N" & i).Formula = "=SUM(XLOOKUP(DATE(" & dtLaterFormat & "),Y1:BA1,Y" & i & ":BA" & i & "):XLOOKUP(TODAY(),Y1:BA1,Y" & i & ":BA" & i & "))"

I keep getting stuck. This is what I've got so far but I know it's not right.

With Application.WorksheetFunction

    .Sum(.XLookup(dtLaterFormat, .Index(arr, 0, 1), .Index(arr, i, 2), "Not Found")
    )
    
End With

Help?!

 

[Domenic]

Try...

With Application.WorksheetFunction

    wsData.Range("N" & i).Value = .Sum(.XLookup(dtLaterFormat, .Index(arr, 1, 0), .Index(arr, i, 0), "Not Found"))
   
End With

Note that with .Index(arr, 1, 0), 1 refers to the row, and 0 refers to all columns in the row. Same thing for your other index.

Hope this helps!

 

[Rex2024]

This is phenomenal. I didn’t understand how to get a range within an array so this is perfect. Thank you!

I’ll try it tomorrow and report back.

 

[Domenic]

You're very welcome, I'm glad I could help, cheers!

 

[Rex2024]

26-Jul-23​	25-Jul-23​	24-Jul-23​	23-Jul-23​	22-Jul-23​	21-Jul-23​	20-Jul-23​	19-Jul-23​
0:00​	0:00​	9:22​	4:25​	4:25​	0:00​	0:00​	0:00​

So, the problem I'm running into is the formula is just returning a 0:00, which is the value from the cell returned in the XLookup.

I need to sum a series of columns in one row based upon a start date and end date (which is what I need to XLookup).

So my original code is to use an Xlookup to return the value located under 26-JUL-23, and the 19-JUL-23, and sum the values inbetween.

wsData.Range("N" & i).Formula = "=SUM(XLOOKUP(DATE(" & dtLaterFormat & "),Y1:BA1,Y" & i & ":BA" & i & "):XLOOKUP(TODAY(),Y1:BA1,Y" & i & ":BA" & i & "))"

This code is not working:

TimeSkd = .Sum(.XLookup(dtLookupFormat, .Index(arr, 1, 0), .Index(arr, i, 0), "Not Found"), .XLookup(dtToday, .Index(arr, 1, 0), .Index(arr, i, 0), "NotFound"))

 

[Domenic]

One way might be to first enter the formula in a cell, and then convert the formula into a value...

    With wsData.Range("N" & i)
        .Formula = "=SUM(XLOOKUP(DATE(" & dtLaterFormat & "),Y1:BA1,Y" & i & ":BA" & i & "):XLOOKUP(TODAY(),Y1:BA1,Y" & i & ":BA" & i & "))"
        .Value = .Value
    End With

Otherwise, maybe something like this...

    Dim dtLookupFormat As Date
    Dim dtToday As Date
    Dim colIndex As Long
    Dim TimeSkd As Double
    
    dtLookupFormat = #7/20/2023#
    dtToday = #7/25/2023#
    
    arr = Range("A1:H2").Value
    
    TimeSkd = 0
    For colIndex = LBound(arr, 2) To UBound(arr, 2)
        If arr(1, colIndex) >= dtLookupFormat And arr(1, colIndex) <= dtToday Then
            TimeSkd = TimeSkd + arr(2, colIndex)
        End If
    Next colIndex
    
    Debug.Print TimeSkd

Hope this helps!

 

[Rex2024]

I ended up using a double loop.

For i = 2 to last_row

    ElseIf cCdeadlineDate > dtLookupFormat Then
        For M = 25 To 53
            ColumnDate = arr(1, M)
                If ColumnDate > dtToday Then
                    O = arr(i, M) 'CDate(arr(i, M))
                    N = O + N
                Else
                End If
        Next M
        arr(i, 14) = N

Next i