findnext issues

[mark kubicki]

o.k., i give up; hthe findNext line is causing an error,
anny suggestions (XL2002, XP)

Public Function subHousemateUtility(name As Range)
Application.Volatile
Dim c
Dim firstAddress
Dim subtotal
With Worksheets("Tally Sheet").Range("B3:B10")
Set c = .Find(name.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
subtotal = subtotal + c.Value
Set c = .FindNext(after:=c.Address)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
subHousemateUtility = c.Address
End Function

thanks in advance
mark

 

[Vasant Nanavati]

It would be nice to know what the error was!

I think you need c, not c.Address, as the After argument.

 

[Dave Peterson]

You had one more reply to your previous thread.

o.k., i give up; hthe findNext line is causing an error,
anny suggestions (XL2002, XP)

Public Function subHousemateUtility(name As Range)
Application.Volatile
Dim c
Dim firstAddress
Dim subtotal
With Worksheets("Tally Sheet").Range("B3:B10")
Set c = .Find(name.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
subtotal = subtotal + c.Value
Set c = .FindNext(after:=c.Address)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
subHousemateUtility = c.Address
End Function

thanks in advance
mark

 

[Tom Ogilvy]

According to Dave Peterson, who tested it in xl2002, FindNext does not work
in a UDF used in a worksheet in xl2002.

also, there is an error in your code

Set c = .FindNext(after:=c.Address)
should be

Set c = .FindNext(after:=c)

As written, your function returns the address of the first cell found - so
not sense doing the loop part

What are you trying to have returned by your function - I suspect it can be
done with builtin functions.