Small Find & Replace / Substitute Macro Problem

Gunswick

Board Regular
Joined
Jun 18, 2008
Messages
179
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Do a check

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

Gunswick

Board Regular
Joined
Jun 18, 2008
Messages
179
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,574
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top