Small Find & Replace / Substitute Macro Problem

[Gunswick]

My macro to find and replace (in comments) is working nicely with input boxes, EXCEPT when the user enters the same value into both input boxes (one for the find text, one for the replace with text).

then it just hangs, and excel crashes. I expect its do with using application.substitute or something.

or do i need an if Findwhat = withwhat then ignore

Sub comment_Header_replace()
'replaces the header of a comment, and makes it bold, and changes font size to 10.

'asks user to save and make a backup first
'SaveSheet

FindWhat = InputBox("Enter comment header text to find")
WithWhat = InputBox("Enter comment header text to replace with")

Dim FoundCell As Range
'Dim FindWhat As String
'Dim WithWhat As String

'FindWhat = "monkey"
'WithWhat = "XXX"

Do
Set FoundCell = ActiveSheet.Cells.Find(What:=FindWhat, _
After:=ActiveCell, _
LookIn:=xlComments, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If FoundCell Is Nothing Then
Exit Do

Else
FoundCell.Comment.Text _
Application.Substitute(FoundCell.Comment.Text, _
FindWhat, WithWhat)

For Each x In ActiveSheet.Comments
With x.Shape.TextFrame
.Characters.Font.Bold = False
.Characters(1, Len(WithWhat)).Font.Bold = True
.Characters.Font.Size = 10
.AutoSize = True
End With
Next x

End If

Loop
End Sub

 

[DonkeyOte]

Do a check

If FindWhat = WithWhat Then 
    MsgBox "Invalid Search - Routine to Terminate",vbcritical,"Error"
    Exit Sub
End If

 

[Gunswick]

I need it to actually replace it though (with the same text), so that my other macro that replaces "body text" in the comments instead (i.e. not bold writing),

and then calls the header replace macro above, once its finished (otherwise the header becomes not bold! and the whole comment box becomes not bold).

so i need it to allow the same value for both, and run correctly by still actually replacing it.

thanks for the help today mate LASW10, you have been very helpful in both threads :D

Sub comment_TextBody_replace()
'replaces the text of a comment, and makes it not-bold, and changes font size to 10.
'it then runs the header replace to make the header bold again.

'asks user to save and make a backup first
'SaveSheet

FindWhat = InputBox("Enter comment body text to find")
WithWhat = InputBox("Enter comment body text to replace with")

Dim FoundCell As Range
'Dim FindWhat As String
'Dim WithWhat As String

'FindWhat = "super"
'WithWhat = "duper"

Do
Set FoundCell = ActiveSheet.Cells.Find(What:=FindWhat, _
After:=ActiveCell, _
LookIn:=xlComments, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do

Else
FoundCell.Comment.Text _
Application.Substitute(FoundCell.Comment.Text, _
FindWhat, WithWhat)
For Each x In ActiveSheet.Comments
With x.Shape.TextFrame
.Characters.Font.Bold = False
.Characters(1, Len(WithWhat)).Font.Bold = False
.Characters.Font.Size = 10
.AutoSize = True

End With
Next x

End If

Loop

MsgBox "Enter header name twice (to keep it bold), e.g. Technology Services (do not need : "

comment_Header_replace

End Sub