v.basic misc programming ?
"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
|