Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Programming for Excel | New Users to Excel | |||
misc | Excel Discussion (Misc queries) | |||
Visual Basic programming | Excel Discussion (Misc queries) | |||
A few problems Programming to the Visual Basic Editor | Excel Programming | |||
Visual Basic Programming Difficulties with linking Userforms | Excel Programming |