Charles,
I thought I had qualified my ranges using:-
STR_RNG_Name = "'" & Var_SheetZZ & "'!" & Var_Name.Address
STR_RNG_Date = "'" & Var_SheetZZ & "'!" & Var_Date.Address
Where Var_SheetZZ = Application.Caller.Parent.Name (returns the name of the
worksheet that called the function I think).
So If I have qualified my ranges (I think I have) I still have the same
problem. Bizarely The function works on one sheet but on the other sheet it
now gets errors of
#Value!.
Thankx
Andi
"Charles Williams" wrote in message
...
Hi Andi,
A little-known quirk of Application.evaluate (which is the default for
Evaluate) is that unqualified range references (range references without a
worksheet qualifier) are always assumed to refer to the active worksheet.
Probably the simplest solution is to use Worksheet.evaluate instead:
HolAvail = Application.caller.parent.Evaluate("SUMPRODUCT((" &
STR_RNG_Date
& _
"=Hol_Start)*(" & STR_RNG_Date & _
"<=Hol_End)*(" & STR_RNG_Name & _
"=Hol_Name)*(Hol_Type_Code))")
Alternatively you can construct the string to use range references
including
the worksheet qualifier.
See http://www.decisionmodels.com/calcsecretsh.htm for an example and some
limitations of the evaluate method.
There is also a Chart.Evaluate method, but I have never used it so I can
only guess that it might be useful for Series formulae, maybe when the
sheet
is a chartsheet?
regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com
"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