Mark,

I think this is the offending line

If .Range("B" & CStr(r)).Value =
worksheets(sheet1).range("Name").value Then

it should be

If .Range("B" & CStr(r)).Value =
worksheets(sheet1).range(Name).value Then

otherwise it woill look for a workbook name called 'Name'

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"mark kubicki" wrote in message
...
something basic is wrong, (boy do i feel stupid)

on my worksheet i have the user defined funtcion entered like this:
TotalAmount(E11,B20)

the function in part reads like this (i've reduced it as much as possible,
so as is, it may sound a little curious, but this should isolate my
problem -i don't work) :

Public Function TotalAmount(Name)
Application.Volatile
Dim r as integer
r = 3
Dim SubTotal
SubTotal = 0
With Worksheets(Sheet2)
Do While r < 250
If .Range("B" & CStr(r)).Value =
worksheets(sheet1).range("Name").value Then
SubTotal = SubTotal + .Range("D" & CStr(r)).Value
End If
r = r + 1
Loop
End With
TotalAmount = SubTotal
End Function


thanks in advance,
mark