"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)


OK Problem no 1

VBA tinks you are passing two variables of type Variant
one called E11 and the other called B20. What you are trying to
do is pass a string so change the call to 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)


Ok more problems

You have told VBA your are passing in a variable called Name
but not told it what type it is. Change it and while tehre use a
different
variable, Name has a special meaninf

Public Function TotalAmount(MyRangeName as String)

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

By putting Name in quotes you have passed it the literal value Name instead
of the value you passed in the variable So change this to

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


SubTotal = SubTotal + .Range("D" & CStr(r)).Value
End If
r = r + 1
Loop
End With
TotalAmount = SubTotal
End Function



Keith