A
Andibevan
Hi All,
The following function works fine, but only on for the worksheet which the
user is currently looking at.
Can anyone provide some guidance so that this code might work on 3-4
different worksheets within 1 workbook?
Function HolAvail()
Application.Volatile
Dim Var_Name As Range 'Location of Name
Dim Var_Date As Range 'Location of Date
Dim Var_Name_Row As String 'Name row
Dim Var_Date_Column As String 'Date Column
Var_Date_Column = Application.Caller.Column 'Column where date is
'Converts Column number into Column Letter
If Var_Date_Column > 26 Then
Var_Date_Column = Chr(Int((Var_Date_Column - 1) / 26) + 64) & _
Chr(((Var_Date_Column - 1) Mod 26) + 65)
Else
' Columns A-Z
Var_Date_Column = Chr(Var_Date_Column + 64)
End If
Set Var_Date = Range(Var_Date_Column & "2")
Var_Name_Row = Application.Caller.Row 'Row where name is
Set Var_Name = Range("A" & Var_Name_Row)
'Criteria to lookup against holiday / resource list
HolAvail = Evaluate("SUMPRODUCT((" & Var_Date.Address & _
">=Hol_Start)*(" & Var_Date.Address & _
"<=Hol_End)*(" & Var_Name.Address & _
"=Hol_Name)*(Hol_Type_Code))")
If (Evaluate("SUMPRODUCT((" & Var_Date.Address & _
">=Hol_Start)*(" & Var_Date.Address & _
"<=Hol_End)*( Hol_Name =""Public Holiday"")*(Hol_Type_Code))"))
= 2 Then
HolAvail = 2
Else
HolAvail = HolAvail
End If
'SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Name="Public
Holiday")*(Hol_Type_Code))
'If (Evaluate(SumProduct((I2 = Hol_Start) * (I2 <= Hol_End) * (Hol_Name =
"Public Holiday") * (Hol_Type_Code)))) Then
'End If
'Workdays are Blank
HolAvail = IIf(HolAvail = 0, "", HolAvail)
'Weekends are W (Weekdays 7 & 1)
HolAvail = IIf((Weekday(Var_Date) = 7) Or (Weekday(Var_Date) = 1), _
"W", HolAvail)
End Function
Ta
Andi
The following function works fine, but only on for the worksheet which the
user is currently looking at.
Can anyone provide some guidance so that this code might work on 3-4
different worksheets within 1 workbook?
Function HolAvail()
Application.Volatile
Dim Var_Name As Range 'Location of Name
Dim Var_Date As Range 'Location of Date
Dim Var_Name_Row As String 'Name row
Dim Var_Date_Column As String 'Date Column
Var_Date_Column = Application.Caller.Column 'Column where date is
'Converts Column number into Column Letter
If Var_Date_Column > 26 Then
Var_Date_Column = Chr(Int((Var_Date_Column - 1) / 26) + 64) & _
Chr(((Var_Date_Column - 1) Mod 26) + 65)
Else
' Columns A-Z
Var_Date_Column = Chr(Var_Date_Column + 64)
End If
Set Var_Date = Range(Var_Date_Column & "2")
Var_Name_Row = Application.Caller.Row 'Row where name is
Set Var_Name = Range("A" & Var_Name_Row)
'Criteria to lookup against holiday / resource list
HolAvail = Evaluate("SUMPRODUCT((" & Var_Date.Address & _
">=Hol_Start)*(" & Var_Date.Address & _
"<=Hol_End)*(" & Var_Name.Address & _
"=Hol_Name)*(Hol_Type_Code))")
If (Evaluate("SUMPRODUCT((" & Var_Date.Address & _
">=Hol_Start)*(" & Var_Date.Address & _
"<=Hol_End)*( Hol_Name =""Public Holiday"")*(Hol_Type_Code))"))
= 2 Then
HolAvail = 2
Else
HolAvail = HolAvail
End If
'SUMPRODUCT((I2=Hol_Start)*(I2<=Hol_End)*(Hol_Name="Public
Holiday")*(Hol_Type_Code))
'If (Evaluate(SumProduct((I2 = Hol_Start) * (I2 <= Hol_End) * (Hol_Name =
"Public Holiday") * (Hol_Type_Code)))) Then
'End If
'Workdays are Blank
HolAvail = IIf(HolAvail = 0, "", HolAvail)
'Weekends are W (Weekdays 7 & 1)
HolAvail = IIf((Weekday(Var_Date) = 7) Or (Weekday(Var_Date) = 1), _
"W", HolAvail)
End Function
Ta
Andi