Passing an array from a UDF to a workday excel function

Masha92

Board Regular
Joined
Jan 27, 2019
Messages
51
Office Version
  1. 365
Hi guys,

I failed to use the named ranges to fulfill the requirement I need in my project. So I thought of something else via VBA and I hope someone can help me with it :)

I have a list of holidays in a column and another column with specific criteria "GS1, GS2...etc"

I wrote the below code as a function:

Code:
Public Function Index_holidays(K As String) As Variant

Dim Arr1() As Variant, I As Integer
Dim sht1 As Worksheet: Set sht1 = Sheets("Index Holiday Calendar")

For Each R In sht1.Range("A3:A" & sht1.Cells(Cells.Rows.Count, "A").End(xlUp).Row)

    If R.Value = "gs1" Then
        ReDim Preserve Arr1(0 To I)
        Arr1(I) = R.Offset(0, 12).Value
        I = I + 1
    End If
  
Next R
End Function

The main idea is to get a customized list of holidays based on a criteria and use it in a workday function like that =WORKDAY(Q3,5,Index_holidays(A3)). Where A3 is the criteria "gs1 for example".

I hope my idea is clear.

Many thanks to you in advance, you have been so helpful before! :)

Best,
Masha
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi, you could use a simple array formula instead - for example.

Book1
ABCDEF
1CrtieriaHolidayDateCriteriaResult
2GS101/01/201931/12/2018GS207/01/2019
3GS101/02/201931/12/2018GS108/01/2019
4GS101/03/2019
5GS201/04/2019
6GS201/05/2019
7GS201/06/2019
Sheet1
Cell Formulas
RangeFormula
F2:F3F2{=WORKDAY(D2,5,IF($A$2:$A$7=E2,$B$2:$B$7,0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
Omg! How come I didnt think of this!

I will try it right away! Thanks alot...!!!!
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top