ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Code Only works when sheet is in focus (https://www.excelbanter.com/excel-programming/341097-code-only-works-when-sheet-focus.html)

Andibevan[_4_]
Code Only works when sheet is in focus
 
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

Tom Ogilvy
Code Only works when sheet is in focus
 
all your unqualifed ranges would refer to the activesheet.

for example,

Set Var_Date = Range(Var_Date_Column & "2")

refers to the activesheet.

You would need to qualify these ranges with the sh reference

Dim sh as Worksheet
set sh = Application.Caller.Parent

Set Var_Date = sh.Range(Var_Date_Column & "2")

as an example.

--
Regards,
Tom Ogilvy



"Andibevan" wrote in message
...
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

Andibevan[_4_]
Code Only works when sheet is in focus
 
Thanks Tom - I will re-write my code using your suggestion.

Ta



"Tom Ogilvy" wrote in message
...
all your unqualifed ranges would refer to the activesheet.

for example,

Set Var_Date = Range(Var_Date_Column & "2")

refers to the activesheet.

You would need to qualify these ranges with the sh reference

Dim sh as Worksheet
set sh = Application.Caller.Parent

Set Var_Date = sh.Range(Var_Date_Column & "2")

as an example.

--
Regards,
Tom Ogilvy



"Andibevan" wrote in message
...
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

Bernie Deitrick
Code Only works when sheet is in focus
 
Andi,

Get rid of all this:

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)


and use

Set Var_Date = Application.Caller.Parent.Cells(2, Application.Caller.Column)
Set Var_Name = Application.Caller.Parent.Cells(Application.Caller .Row, 1)

HTH,
Bernie
MS Excel MVP


"Andibevan" wrote in message
...
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

Andibevan[_4_]
Code Only works when sheet is in focus
 
Bernie,

Thanks for the help - I tried you suggestion and it still only works on the
sheet I that is active.

Any ideas?

Ta

Andi

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Andi,

Get rid of all this:

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)


and use

Set Var_Date = Application.Caller.Parent.Cells(2,
Application.Caller.Column)
Set Var_Name = Application.Caller.Parent.Cells(Application.Caller .Row, 1)

HTH,
Bernie
MS Excel MVP


"Andibevan" wrote in message
...
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

Andibevan[_4_]
Code Only works when sheet is in focus
 
Tom,

I changed Var_Name and Var_Date as suggested but it still only works on the
sheet in focus.

Do I need to change the part of HolAvail = Evaluate("SUMPRODUCT......?

Ta

Andi


"Tom Ogilvy" wrote in message
...
all your unqualifed ranges would refer to the activesheet.

for example,

Set Var_Date = Range(Var_Date_Column & "2")

refers to the activesheet.

You would need to qualify these ranges with the sh reference

Dim sh as Worksheet
set sh = Application.Caller.Parent

Set Var_Date = sh.Range(Var_Date_Column & "2")

as an example.

--
Regards,
Tom Ogilvy



"Andibevan" wrote in message
...
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

Bernie Deitrick
Code Only works when sheet is in focus
 
Andi,

Try changing the SUMPRODUCT formula lines to:

HolAvail = Evaluate("SUMPRODUCT(('" & Var_Date.Parent.Name & _
"'!" & Var_Date.Address & _
"=Hol_Start)*('" & Var_Date.Parent.Name & _
"'!" & Var_Date.Address & _
"<=Hol_End)*('" & Var_Name.Parent.Name & _
"'!" & Var_Name.Address & _
"=Hol_Name)*(Hol_Type_Code))")

If (Evaluate("SUMPRODUCT(('" & Var_Date.Parent.Name & _
"'!" & Var_Date.Address & _
"=Hol_Start)*('" & Var_Date.Parent.Name & _
"'!" & Var_Date.Address & _
"<=Hol_End)*( Hol_Name =""Public Holiday"")*(Hol_Type_Code))")) = 2 Then

HTH,
Bernie
MS Excel MVP


"Andibevan" wrote in message
...
Bernie,

Thanks for the help - I tried you suggestion and it still only works on the
sheet I that is active.

Any ideas?

Ta

Andi

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Andi,

Get rid of all this:

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)


and use

Set Var_Date = Application.Caller.Parent.Cells(2,
Application.Caller.Column)
Set Var_Name = Application.Caller.Parent.Cells(Application.Caller .Row, 1)

HTH,
Bernie
MS Excel MVP


"Andibevan" wrote in message
...
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

Tom Ogilvy
Code Only works when sheet is in focus
 
Sure - a string is a string - there isn't any more information in the string
than you place there. If you string does not have the sheet locations
included, it will refer to the activesheet - same as it would in a cell.

--
Regards,
Tom Ogilvy

"Andibevan" wrote in message
...
Tom,

I changed Var_Name and Var_Date as suggested but it still only works on
the
sheet in focus.

Do I need to change the part of HolAvail = Evaluate("SUMPRODUCT......?

Ta

Andi


"Tom Ogilvy" wrote in message
...
all your unqualifed ranges would refer to the activesheet.

for example,

Set Var_Date = Range(Var_Date_Column & "2")

refers to the activesheet.

You would need to qualify these ranges with the sh reference

Dim sh as Worksheet
set sh = Application.Caller.Parent

Set Var_Date = sh.Range(Var_Date_Column & "2")

as an example.

--
Regards,
Tom Ogilvy



"Andibevan" wrote in message
...
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


All times are GMT +1. The time now is 11:13 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com