v.basic misc programming ?

M

mark kubicki

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
 
T

Tom Ogilvy

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

mark kubicki

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

Stephen Bye

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?
 
B

Bob Phillips

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

Tom Ogilvy

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
 
K

Keith Willshaw

mark kubicki said:
something basic is wrong, (boy do i feel stupid)

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

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)
Click to expand...


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 =
Click to expand...
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
Click to expand...


Keith
 
Top