置換

連続置換

Sub 置換_連続置換()
    'シートの情報で、連続置換する。
    Dim s As Worksheet    Dim s1 As Worksheet
    Dim r_end As Integer    Dim i As Integer
    Set s = ActiveSheet    With s.UsedRange        r_end = .Row + .Rows.Count - 1    End With        Set s1 = ThisWorkbook.Sheets("置換")        For i = 1 To r_end        If s1.Cells(i, 1).Value <> "" Then            s.Cells.Replace What:=s1.Cells(i, 1).Value, Replacement:=s1.Cells(i, 2).Value, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False        End If    Next i
End Sub

置換①

Sub 置換_置換①()
' Selection.Replace What:="１．", Replacement:="１ ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2' Selection.Replace What:="２．", Replacement:="２ ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2' Selection.Replace What:="３．", Replacement:="３ ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2' Selection.Replace What:="４．", Replacement:="４ ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2' Selection.Replace What:="５．", Replacement:="５ ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
'サービス配達先 Selection.Replace What:="サービス配達先", Replacement:="サービス配送先", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '（ハイフン付き） Selection.Replace What:="（ハイフン付き）", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '要介護認定状況コード（未申請～介護５） Selection.Replace What:="要介護認定状況コード（未申請～介護５）", Replacement:="要介護度", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '個人番号／移行データ番号（任意） Selection.Replace What:="個人番号／移行データ番号（任意）", Replacement:="移行データ番号（任意）", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '決定年月日（最終） Selection.Replace What:="決定年月日（最終）", Replacement:="決定年月日", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '決定年月日　決定 Selection.Replace What:="決定年月日　決定", Replacement:="決定区分", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '決定年月日　変更 Selection.Replace What:="決定年月日　変更", Replacement:="決定区分", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '決定年月日　廃止 Selection.Replace What:="決定年月日　廃止", Replacement:="決定区分", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '決定年月日　却下 Selection.Replace What:="決定年月日　却下", Replacement:="決定区分", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '決定区分　（最終） Selection.Replace What:="決定区分　（最終）", Replacement:="決定区分", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '世帯市民 Selection.Replace What:="世帯市民", Replacement:="世帯", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '介護保険料の所得段階区分 Selection.Replace What:="介護保険料の所得段階区分", Replacement:="階層（減免ありの所得段階区分）", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '決定以外の理由（最終） Selection.Replace What:="決定以外の理由（最終）", Replacement:="決定以外の理由", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '決定年月日 Selection.Replace What:="決定年月日", Replacement:="決定年月日" & Chr(10) & "決定区分", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '決定区分 Selection.Replace What:="決定区分", Replacement:="決定年月日" & Chr(10) & "決定区分", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False '階層 Selection.Replace What:="階層", Replacement:="階層（減免ありの所得段階区分）", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

置換②

Sub 置換_置換②()
Selection.Replace What:="「", Replacement:="""", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 Selection.Replace What:="」", Replacement:="""", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

カッコ_全角

Sub 置換_カッコ_全角()
Selection.Replace What:="（", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 Selection.Replace What:="）", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

数字_全角

Sub 置換_数字_全角() Selection.Replace What:="１．", Replacement:="１ ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 Selection.Replace What:="２．", Replacement:="２ ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 Selection.Replace What:="３．", Replacement:="３ ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 Selection.Replace What:="４．", Replacement:="４ ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 Selection.Replace What:="５．", Replacement:="５ ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

Page updated

Google Sites

Report abuse