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![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
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:
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!![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
Best,
Masha
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: