If function coded incorrectly ..by me

[60john]

Can someone tell me what is wrong with this please?

Cells.Select
If Selection.Find(What:="Ship 1", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate Is True Then
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Lady Luck"
End If

 

[nuked]

Pretty much everything.

What are you trying to achieve.

 

[60john]

Pretty much everything.

What are you trying to achieve.

It's just a small piece of code in a larger macro.

I am trying to search an entire workbook for a cell containing the text "Ship 1". If it is found I want the text in the cell to be changed to "Lady Luck"

 

[nuked]

There's probably a better way, but this should do:

Option Explicit
Sub ReplaceShipWithLadyLuck()
Dim rng As Range
Dim wks As Worksheet
For Each wks In Worksheets
Set rng = wks.Cells.Find("Ship1")
If Not rng Is Nothing Then
    rng = "Lady Luck"
    Exit Sub
    
End If
Next wks

End Sub

 

[60john]

There's probably a better way, but this should do:

Option Explicit
Sub ReplaceShipWithLadyLuck()
Dim rng As Range
Dim wks As Worksheet
For Each wks In Worksheets
Set rng = wks.Cells.Find("Ship1")
If Not rng Is Nothing Then
    rng = "Lady Luck"
    Exit Sub
    
End If
Next wks

End Sub

Thanks nuked. That did the trick.