Custom XIRR function for each client

I am new to VBA and new to message boards. Please excuse any mistakes.

I have a spreadsheet of client information (approx. 10,000 clients), including client id, cashflows and dates in different columns. The dates are all sorted by each client. I would like to run the built in XIRR function once for each client using the cashflows and date information associated with their specific client id. I created a separate column with a "1" in each row that is the last row for a client. I was planning to run "=if(X2=1, abc123(...), "") in a new column so that it only runs the script when it sees that 1.

The script I created is pieced together from things I found online.

My function looks like this: =abc123(A2,A:A,S:S,M:M)

I am receiving a #ref error when I run this script:

Code:

---

Sub abc123(client_num, cin_client_id_col, cashflows_col, dates_col)

'runs internal rate of return at the client level

'--------------------------------------------
' create variant objects of length n to store values from range inputs\
'--------------------------------------------
n = WorksheetFunction.CountA(cin_client_id_col) 'number of elements in input ranges

ReDim client_array(1 To n, 1) As Variant
ReDim value_array(1 To n, 1) As Variant
ReDim date_array(1 To n, 1) As Variant

client_array = cin_client_id_col
value_array = cashflows_col
date_array = dates_col

'--------------------------------------------
' check if client array values are equal to the desired client key
'--------------------------------------------

ReDim client_key(1 To n, 1) As Variant

For i = 1 To n

If client_num = client_array(i, 1) Then
    client_key(i, 1) = 1
    Else
    client_key(i, 1) = 0
End If
Next

'--------------------------------------------
' throw out clients that aren't used
'--------------------------------------------

Dim count As Integer
count = WorksheetFunction.Sum(client_key)

ReDim data_mat(1 To count, 2) As Variant

k = 1

For j = 1 To n

If client_key(i, 1) = 1 Then
    data_mat(k, 1) = value_array(j, 1)
    data_mat(k, 2) = date_array(j, 1)

End If
    k = k + 1
Next

'--------------------------------------------

n = count

ReDim values_client(1 To n, 1)
ReDim dates_client(1 To n, 1)

For i = 1 To n
    values_client(i, 1) = data_mat(i, 1)
    dates_client(i, 1) = data_mat(i, 2)

Next

abc123 = WorksheetFunction.Xirr(values_client, dates_client)

'=client_irr(A2,A:A,S:S,M:M)

End Sub

---

Any guidance would be greatly appreciated.

to return a value you should be using a function not a sub, maybe this is a typo in your post

if you pass client_num as a range, you can use excel find method to find which row next row has 1 in column X, then your range of rows is defined

try like

vb Code:

function abc123(c_num as range, cin_client_id_col, cashflows_col, dates_col) ' other 
'parameters types should also be specified, as should the return from the function
client_num = c_num.value
lastrow = range("x:x").find(1, c_num.offset(, 23))   ' find next 1 in column X
 
'range for client would then be from c_num.row to lastrow
 
'runs internal rate of return at the client level
 
'--------------------------------------------
' create variant objects of length n to store values from range inputs\
'--------------------------------------------
n = WorksheetFunction.CountA(cin_client_id_col) 'number of elements in input ranges