워크 시트와 주석을 동시에 바꾸기 (Microsoft Excel)

Sub ReplaceNames()

Dim sDefaultName As String     Dim sNewName As String     Dim sht As Worksheet     Dim cmt As Comment     Dim wks As Worksheet     Dim sCmt As String     Dim shp As Shape

sDefaultName = "Jason Anderson"

sNewName = InputBox("Enter your first and last name.", _       "Name Replacement")



' Replace cell text     For Each sht In ActiveWorkbook.Worksheets         sht.Cells.Replace what:=sDefaultName, _           Replacement:=sNewName, LookAt:=xlPart, _           SearchOrder:=xlByRows, MatchCase:=False, _           SearchFormat:=False, ReplaceFormat:=False     Next sht

' Replace comment text     For Each wks In ActiveWorkbook.Worksheets         For Each cmt In wks.Comments             sCmt = cmt.Text             If InStr(sCmt, sDefaultName) <> 0 Then                 sCmt = Application.WorksheetFunction. _                   Substitute(sCmt, sDefaultName, sNewName)

cmt.Text Text:=sCmt             End If         Next cmt     Next wks     Set wks = Nothing     Set cmt = Nothing

' Replace text box text     On Error Resume Next     For Each wks In ActiveSheet.Parent.Worksheets         For Each shp In wks.Shapes             With shp.TextFrame.Characters                 .Text = Application.WorksheetFunction.Substitute( _                   .Text, sDefaultName, sNewName)

End With         Next shp     Next wks End Sub

=MyName