Overflow error

[zakynthos]

Hi,

I'm taking a course on VBA and have tried out the following code for the 'find' function, ie to find and replace occurrences of 'dog' with 'dog found'

I'm getting an error message about 'overflow' but I believe this relates to "assignment, calculation, or data type conversion is too large to be represented within the range of values allowed for that type of variable." But I'm just trying to 'find' dog - that's only 3 letters so this error message must relate to another part of the code.

Anyway, here it is - can anyone offer a reason for the error? Als, I've removed the 'matchcase:=matchcase' which occurred after SearchDirection:=xlNext because that also resulted in an error message 'variable not defined':


Sub macro1()

Dim result As Range
Dim searchterm As String
searchterm = "dog"


ActiveSheet.Select

Do

With ActiveSheet.Cells

Set result = .find(What:=searchterm, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)

result.Select

'change value so it won't find cell again
ActiveCell.Value = ActiveCell.Value & " ##found##"
Set result = .FindNext(result)
End With

Loop Until result Is Nothing

End Sub

 

[VoG]

Try removing the part in red

Set result = .find(What:=searchterm, After:=.Cells(.Cells.Count),  LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,  SearchDirection:=xlNext)

 

[zakynthos]

Thanks very much for your help - now works perfectly!