FindNext problem
 

this should be simple, but my FindNext line is causing an error; any
suggestions?

on the wroksheets("Sheet1") is entered:
TotalHousemateUtility(A1); the value of A1 is a string

Public Function TotalHousemateUtility(Name)
Dim firstAddress
Dim c As Range
Dim subTotal as Single
subTotal = 0
With Worksheets("Sheet 2").Range("B3:B10")
Set c = .Find(Name, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
subTotal = subTotal + Worksheets("Tally
Sheet").Range(c.Address).Offset(0, 2).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
TotalHousemateUtility = subTotal
End Function

FindNext problem
 

If you are using a version of Excel prior to xl2002, then Find does not work
in a function used in a worksheet.

Other than that, your code looks ok. Have you tried calling the Function
from a VBA module and see if it works that way? I suspect it does.

--
Regards,
Tom Ogilvy


"mark kubicki" wrote in message
...
this should be simple, but my FindNext line is causing an error; any
suggestions?

on the wroksheets("Sheet1") is entered:
TotalHousemateUtility(A1); the value of A1 is a string

Public Function TotalHousemateUtility(Name)
Dim firstAddress
Dim c As Range
Dim subTotal as Single
subTotal = 0
With Worksheets("Sheet 2").Range("B3:B10")
Set c = .Find(Name, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
subTotal = subTotal + Worksheets("Tally
Sheet").Range(c.Address).Offset(0, 2).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
TotalHousemateUtility = subTotal
End Function

FindNext problem
 

tom, (i'm scratching my head)
i've got XL2002, and am using it in a VBA,
maybe i ought to try re-entering the whole procedure?



"Tom Ogilvy" wrote in message
...
If you are using a version of Excel prior to xl2002, then Find does not
work
in a function used in a worksheet.

Other than that, your code looks ok. Have you tried calling the Function
from a VBA module and see if it works that way? I suspect it does.

--
Regards,
Tom Ogilvy


"mark kubicki" wrote in message
...
this should be simple, but my FindNext line is causing an error; any
suggestions?

on the wroksheets("Sheet1") is entered:
TotalHousemateUtility(A1); the value of A1 is a string

Public Function TotalHousemateUtility(Name)
Dim firstAddress
Dim c As Range
Dim subTotal as Single
subTotal = 0
With Worksheets("Sheet 2").Range("B3:B10")
Set c = .Find(Name, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
subTotal = subTotal + Worksheets("Tally
Sheet").Range(c.Address).Offset(0, 2).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
TotalHousemateUtility = subTotal
End Function

FindNext problem
 

First I would get it working outside using it in the spread sheet, then if
it doesn't work in the spreadsheet you will have to find another way. My
statement that is works in UDF's in versions later than xl2000 is base on
what Dave Peterson has stated in this group. I don't have first hand
knowledge that it does, but I know it doesn't in XL2000 and earlier.
--
Regards,
Tom Ogilvy

"mark kubicki" wrote in message
...
tom, (i'm scratching my head)
i've got XL2002, and am using it in a VBA,
maybe i ought to try re-entering the whole procedure?



"Tom Ogilvy" wrote in message
...
If you are using a version of Excel prior to xl2002, then Find does not
work
in a function used in a worksheet.

Other than that, your code looks ok. Have you tried calling the
Function
from a VBA module and see if it works that way? I suspect it does.

--
Regards,
Tom Ogilvy


"mark kubicki" wrote in message
...
this should be simple, but my FindNext line is causing an error; any
suggestions?

on the wroksheets("Sheet1") is entered:
TotalHousemateUtility(A1); the value of A1 is a string

Public Function TotalHousemateUtility(Name)
Dim firstAddress
Dim c As Range
Dim subTotal as Single
subTotal = 0
With Worksheets("Sheet 2").Range("B3:B10")
Set c = .Find(Name, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
subTotal = subTotal + Worksheets("Tally
Sheet").Range(c.Address).Offset(0, 2).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
TotalHousemateUtility = subTotal
End Function

FindNext problem
 

I'll stick with "Find works ok in UDF's called from a cell in xl2002" (not sure
about 2003) <vbg.

But .findnext doesn't work nicely at all (at least in xl2002).

But the OP could use a UDF like:

Option Explicit
Public Function TotalHousemateUtility(myName)

Dim NameRng As Range
Dim TallyRng As Range
Dim subtotal As Double

Set NameRng = Worksheets("sheet 2").Range("b3:b10")
Set TallyRng = Worksheets("tally sheet") _
.Range(NameRng.Address).Offset(0, 2)
subtotal = Application.SumIf(NameRng, myName, TallyRng)

TotalHousemateUtility = subtotal

End Function

Or just a worksheet function:
=SUMIF('Sheet 2'!B3:B10,A1,'Tally Sheet'!D3:D10)

ps. I changed the passed parm from Name to myName. I don't like using VBA
keywords as variables. Excel is usually forgiving, but it gets me confused.

And if I were really going to use a UDF, I think I'd want to pass it the ranges
to search through and the range to sum.

Then excel would know that when a value in those ranges changed, it would need
to recalculate.




Tom Ogilvy wrote:

First I would get it working outside using it in the spread sheet, then if
it doesn't work in the spreadsheet you will have to find another way. My
statement that is works in UDF's in versions later than xl2000 is base on
what Dave Peterson has stated in this group. I don't have first hand
knowledge that it does, but I know it doesn't in XL2000 and earlier.
--
Regards,
Tom Ogilvy

"mark kubicki" wrote in message
...
tom, (i'm scratching my head)
i've got XL2002, and am using it in a VBA,
maybe i ought to try re-entering the whole procedure?



"Tom Ogilvy" wrote in message
...
If you are using a version of Excel prior to xl2002, then Find does not
work
in a function used in a worksheet.

Other than that, your code looks ok. Have you tried calling the
Function
from a VBA module and see if it works that way? I suspect it does.

--
Regards,
Tom Ogilvy


"mark kubicki" wrote in message
...
this should be simple, but my FindNext line is causing an error; any
suggestions?

on the wroksheets("Sheet1") is entered:
TotalHousemateUtility(A1); the value of A1 is a string

Public Function TotalHousemateUtility(Name)
Dim firstAddress
Dim c As Range
Dim subTotal as Single
subTotal = 0
With Worksheets("Sheet 2").Range("B3:B10")
Set c = .Find(Name, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
subTotal = subTotal + Worksheets("Tally
Sheet").Range(c.Address).Offset(0, 2).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
TotalHousemateUtility = subTotal
End Function







--

Dave Peterson