UDF only works when focus is on a specific sheet - Help!!
 

Tom - Thanks but It is already in the general module.


"Tom Ogilvy" wrote in message
...
Is the code in a general module as opposed to the worksheet module. If
not,
try putting it in a general module.

--
Regards,
Tom Ogilvy


"Andibevan" wrote in message
...
Hi All,

I have been struggling with the following UDF that I keep nearly get
working
but then it gives up.

It is a stand-alone UDF that needs no variables as it determines the
date
it
should work on from the 2nd row of the column it is called from, and the
name to look up from column A of the row it is called from.

The problem is that it works fine when calculate is hit (F9) from one
worksheet but it only generates #REF! when called (or forced to
calculate
using F9) from another.

I am completely stumped:-

Function HolAvail()

Application.Volatile

On Error GoTo Err:

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

Dim Var_SheetZZ As String ' as worksheets?

Dim STR_RNG_Date As String

Dim STR_RNG_Name As String

Var_SheetZZ = Application.Caller.Parent.Name

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





Var_Name_Row = Application.Caller.Row 'Row where name is



Set Var_Date = Range(Var_Date_Column & "2")

Set Var_Name = Range("A" & Var_Name_Row)



STR_RNG_Name = "'" & Var_SheetZZ & "'!" & Var_Name.Address

STR_RNG_Date = "'" & Var_SheetZZ & "'!" & Var_Date.Address



HolAvail = Evaluate("SUMPRODUCT((" & STR_RNG_Date & _

"=Hol_Start)*(" & STR_RNG_Date & _

"<=Hol_End)*(" & STR_RNG_Name & _

"=Hol_Name)*(Hol_Type_Code))")





If (Evaluate("SUMPRODUCT((" & STR_RNG_Date & _

"=Hol_Start)*(" & STR_RNG_Date & _

"<=Hol_End)*( Hol_Name =""Public
Holiday"")*(Hol_Type_Code))"))

= 2 Then



HolAvail = 2

Else

HolAvail = HolAvail

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)

Err:

'HolAvail = ""

HolAvail = "E"

End Function