Sorry this is a long post but I've been working on this forever!

I have been trying to develop a function using Find/FindNext to search
for text.

The code below is just the start of the development. What it tries to do
is look in a range in a single column and identify all occurrences of a
particular text string.

I started by writing a sub which works fine and identifies all the
occurrences of the string in parameter A (code copied from Chip Pearson):

(Parameter B will be use to compare to a 2nd column, C is the range to
search)
__________________________________________________ ____
Sub FactorX2(A As String, B As String, C As Range)
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddress As String

With C
Set LastCell = .Cells(.Cells.Count)
End With

Set FoundCell = C.Find(what:=A, after:=LastCell, LookAt:=xlWhole)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
End If

Do Until FoundCell Is Nothing
Set FoundCell = C.FindNext(after:=FoundCell)
Debug.Print FoundCell.Address
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End Sub
__________________________________________________ _____

I then tried to convert this to a function:

Function FactorX(A As String, B As String, C As Range) As Variant
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddress As String

With C
Set LastCell = .Cells(.Cells.Count)
End With

Set FoundCell = C.Find(what:=A, after:=LastCell, LookAt:=xlWhole)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
End If

Do Until FoundCell Is Nothing
Set FoundCell = C.FindNext(after:=FoundCell)
Debug.Print "£$ " & FoundCell.Address
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
FactorX = 0
End Function
__________________________________________________ ________

The function finds the first string occurrence, but the
FindNext fails completely. Even if I place Debug.Print Statements before
End Function, nothing is printed. It is as though the
code exits as soon as it hits the FindNext.

Can anyone please shed any light on this?

On 13/07/2010 11:44, ManicMiner17 wrote:
Sorry this is a long post but I've been working on this forever!

I have been trying to develop a function using Find/FindNext to search
for text.


I should have said I'm trying to create a function called from a worksheet.

I found this on Chip Pearson's site:

"If you attempt to change anything, the function will terminate
immediately and return a #VALUE error to the calling cell. In Excel 97
and 2000, a UDF cannot use the Find method of a Range object, even
though that method does not change anything in Excel. This was fixed
with Excel 2002."

Looks like it got broken again in 2003!

I've run into a similar problem, where FindNext does not find subsequent matches. Have you found a solution to yours?

On Tuesday, July 13, 2010 6:44 AM ManicMiner17 wrote:

Sorry this is a long post but I have been working on this forever!

I have been trying to develop a function using Find/FindNext to search
for text.

The code below is just the start of the development. What it tries to do
is look in a range in a single column and identify all occurrences of a
particular text string.

I started by writing a sub which works fine and identifies all the
occurrences of the string in parameter A (code copied from Chip Pearson):

(Parameter B will be use to compare to a 2nd column, C is the range to
search)
__________________________________________________ ____
Sub FactorX2(A As String, B As String, C As Range)
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddress As String

With C
Set LastCell = .Cells(.Cells.Count)
End With

Set FoundCell = C.Find(what:=A, after:=LastCell, LookAt:=xlWhole)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
End If

Do Until FoundCell Is Nothing
Set FoundCell = C.FindNext(after:=FoundCell)
Debug.Print FoundCell.Address
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End Sub
__________________________________________________ _____

I then tried to convert this to a function:

Function FactorX(A As String, B As String, C As Range) As Variant
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddress As String

With C
Set LastCell = .Cells(.Cells.Count)
End With

Set FoundCell = C.Find(what:=A, after:=LastCell, LookAt:=xlWhole)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
End If

Do Until FoundCell Is Nothing
Set FoundCell = C.FindNext(after:=FoundCell)
Debug.Print "?$ " & FoundCell.Address
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
FactorX = 0
End Function
__________________________________________________ ________

The function finds the first string occurrence, but the
FindNext fails completely. Even if I place Debug.Print Statements before
End Function, nothing is printed. It is as though the
code exits as soon as it hits the FindNext.

Can anyone please shed any light on this?


On Tuesday, July 13, 2010 9:55 AM ManicMiner17 wrote:

On 13/07/2010 11:44, ManicMiner17 wrote:

I should have said I am trying to create a function called from a worksheet.

I found this on Chip Pearson's site:

"If you attempt to change anything, the function will terminate
immediately and return a #VALUE error to the calling cell. In Excel 97
and 2000, a UDF cannot use the Find method of a Range object, even
though that method does not change anything in Excel. This was fixed
with Excel 2002."

Looks like it got broken again in 2003!


Submitted via EggHeadCafe
Sterling Database for Silverlight and Windows Phone 7
http://www.eggheadcafe.com/tutorials...s-phone-7.aspx

I've run into a similar problem, where FindNext does not find subsequent matches. Have you found a solution to yours?

On Tuesday, July 13, 2010 6:44 AM ManicMiner17 wrote:

Sorry this is a long post but I have been working on this forever!

I have been trying to develop a function using Find/FindNext to search
for text.

The code below is just the start of the development. What it tries to do
is look in a range in a single column and identify all occurrences of a
particular text string.

I started by writing a sub which works fine and identifies all the
occurrences of the string in parameter A (code copied from Chip Pearson):

(Parameter B will be use to compare to a 2nd column, C is the range to
search)
__________________________________________________ ____
Sub FactorX2(A As String, B As String, C As Range)
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddress As String

With C
Set LastCell = .Cells(.Cells.Count)
End With

Set FoundCell = C.Find(what:=A, after:=LastCell, LookAt:=xlWhole)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
End If

Do Until FoundCell Is Nothing
Set FoundCell = C.FindNext(after:=FoundCell)
Debug.Print FoundCell.Address
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End Sub
__________________________________________________ _____

I then tried to convert this to a function:

Function FactorX(A As String, B As String, C As Range) As Variant
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddress As String

With C
Set LastCell = .Cells(.Cells.Count)
End With

Set FoundCell = C.Find(what:=A, after:=LastCell, LookAt:=xlWhole)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
End If

Do Until FoundCell Is Nothing
Set FoundCell = C.FindNext(after:=FoundCell)
Debug.Print "?$ " & FoundCell.Address
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
FactorX = 0
End Function
__________________________________________________ ________

The function finds the first string occurrence, but the
FindNext fails completely. Even if I place Debug.Print Statements before
End Function, nothing is printed. It is as though the
code exits as soon as it hits the FindNext.

Can anyone please shed any light on this?


On Tuesday, July 13, 2010 9:55 AM ManicMiner17 wrote:

On 13/07/2010 11:44, ManicMiner17 wrote:

I should have said I am trying to create a function called from a worksheet.

I found this on Chip Pearson's site:

"If you attempt to change anything, the function will terminate
immediately and return a #VALUE error to the calling cell. In Excel 97
and 2000, a UDF cannot use the Find method of a Range object, even
though that method does not change anything in Excel. This was fixed
with Excel 2002."

Looks like it got broken again in 2003!


On Sunday, November 28, 2010 11:05 AM MK wrote:

I've run into a similar problem, where FindNext does not find subsequent matches. Have you found a solution to yours?


Submitted via EggHeadCafe
Custom task processes in SharePoint Designer Workflows
http://www.eggheadcafe.com/tutorials...workflows.aspx