v.basic misc programming ?
 

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

v.basic misc programming ?
 

your function accepts one argument, but you are calling it with two. That
would be the first thing to clean up.

--
Regards,
Tom Ogilvy


"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

v.basic misc programming ?
 

OOPS, (in simplifing the function, i forgot to take it out)
it's now fixed below...

"Tom Ogilvy" wrote in message
...
your function accepts one argument, but you are calling it with two. That
would be the first thing to clean up.

--
Regards,
Tom Ogilvy


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

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

v.basic misc programming ?
 

It looks a lot like =SUMIF(Sheet2!B3:B249,name,Sheet2!D3:D249)
What are you trying to do that you think needs a VBA user-defined function?

"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

v.basic misc programming ?
 

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

v.basic misc programming ?
 

This worked for me:

Public Function TotalAmount(Name As Range)
Application.Volatile
Dim r As Integer
r = 3
Dim SubTotal
SubTotal = 0
With Worksheets("Sheet2")
Do While r < 250
If LCase(.Range("B" & CStr(r)).Text) = _
LCase(Name.Value) Then
SubTotal = SubTotal + .Range("D" & CStr(r)).Value
End If
r = r + 1
Loop
End With
TotalAmount = SubTotal
End Function

--
Regards,
Tom Ogilvy

"mark kubicki" wrote in message
...
OOPS, (in simplifing the function, i forgot to take it out)
it's now fixed below...

"Tom Ogilvy" wrote in message
...
your function accepts one argument, but you are calling it with two.
That
would be the first thing to clean up.

--
Regards,
Tom Ogilvy


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

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

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