完全一致する単体の文字列のみ、別シートにある各行・各列指定のキーワードで置換したい

類似の質問もさせていただいていたのですが。
http://q.hatena.ne.jp/1522755461
http://q.hatena.ne.jp/1522512207

今回は「完全一致する場合」のみ、文字列を変えたいのです。
例えばSheet1のG列に、この質問 http://q.hatena.ne.jp/1522512207 の時と似たような状況で、

りんご
りんご ジョナゴールド
りんご ふじ
りんご 紅花




牛肉
牛肉 山形牛
牛肉 松坂牛



お米
お米 コシヒカリ
お米 ササニシキ



というデータが入ってるとします。
そしてSheet2で、「りんご」「牛肉」「お米」を各行に並べ、置換するためのキーワードを列に書き出し、実際に置換する方法を教えていただきました。

今回は「単体の文字列のみを置換」したいのです。
上記の例でいいますと「りんご」「牛肉」「お米」という、単体の文字列のみです。

回答の条件
  • 1人10回まで
  • 登録:2018/04/24 07:49:51
  • 終了:2018/05/14 07:50:05
id:moon-fondu

半角スペース込みの複合文字列(「りんご ジョナゴールド」「牛肉 山形牛」など)は無視しまして、単体の文字列のみを、Sheet2に書き出している指定の文字列に置換できる方法がもしありましたら。
ご教授いただけますと幸いです。

よろしくお願い致します。

ベストアンサー

id:Z1000S No.1

Z1000S回答回数27ベストアンサー獲得回数202018/04/24 08:39:38

ポイント1000pt

完全一致のセルが連続する場合のみを置換対象とするのであれば、
オートフィルタの指定を下記のように変更するだけで大丈夫です。

            'オートフィルタ実行
'            .AutoFilter Field:=1, Criteria1:="=*" & sKeyword & "*"
            .AutoFilter Field:=1, Criteria1:=sKeyword

りんご
りんご ジョナゴールド
りんご ふじ
りんご 紅花

の先頭の「りんご」のような
完全一致のセルが単独で存在する場合も置換対象とするのであれば、
上記の変更に加え、置換処理部のIf、Else部を下記のようにコメント化、および修正して下さい。
(2018/04/24 09:00一部修正)

'        '2行以上連続していたら置換
'        If lEndRow > lBeginRow Then
            With wsTarget
                '置換
'                .Range(.Cells(lBeginRow, TARGET_COL), .Cells(lEndRow, TARGET_COL)).Replace What:=sMainKeywords, Replacement:=sReplaceWords(lReplaceIndex), LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
                .Range(.Cells(lBeginRow, TARGET_COL), .Cells(lEndRow, TARGET_COL)).Value = sReplaceWords(lReplaceIndex)
            End With

            lTargetIndex = lTargetIndex + lTargetCount

            lReplaceIndex = lReplaceIndex + 1
'        Else
'            lTargetIndex = lTargetIndex + 1
'        End If
他8件のコメントを見る
id:Z1000S

勘違いして、先の10個固定版を元にしていました。
申し訳ないです。
これでどうでしょうか

Option Explicit

'置換対象データ関連
Private Const TARGET_SHEET_NAME     As String = "Sheet1"

'フィルターの基準となる列(G列)
Private Const TARGET_COL            As Long = 7


'キーワード関連
Private Const KEYWORDS_SHEET_NAME   As String = "Sheet2"

Private Const KEYWORDS_BEGIN_ROW    As Long = 2

Private Const MAIN_KEYWORDS_COL     As Long = 1

Private Const REPLACE_WORDS_BEGIN_COL   As Long = 2

Private Type ReplaceInformation
    sKeyword        As String
    lCount          As Long
    sReplaceWords() As String
End Type

Public Sub replaceCellsMain()

    Dim wsKeywords      As Worksheet
    Dim wsTarget        As Worksheet
    Dim lKeywordsCount  As Long
    Dim ReplaceInfo     As ReplaceInformation
    Dim lTargetRows()   As Long
    Dim lTargetRowsCount    As Long
    Dim i               As Long

    Application.ScreenUpdating = False

    Set wsTarget = ThisWorkbook.Worksheets(TARGET_SHEET_NAME)
    Set wsKeywords = ThisWorkbook.Worksheets(KEYWORDS_SHEET_NAME)

    '置換するキーワード数取得
    lKeywordsCount = getKeywordsCount(wsKeywords)

    For i = 0 To lKeywordsCount - 1
        '置換するキーワードと置換後の文字列取得
        Call getKeywords(wsKeywords, i, ReplaceInfo)

        'キーワードが含まれる行を格納する配列の初期化
        ReDim lTargetRow(0)

        'キーワードが含まれる行の取得
        lTargetRowsCount = getTargetRows(wsTarget, ReplaceInfo.sKeyword, lTargetRows)

        If lTargetRowsCount > 0 Then
            '置換
            Call replaceCells(wsTarget, lTargetRows, ReplaceInfo)
        End If

        Debug.Print ReplaceInfo.sKeyword & " Done."
    Next i

    Set wsKeywords = Nothing
    Set wsTarget = Nothing

    Application.ScreenUpdating = True

End Sub

Private Function getKeywordsCount(ByRef ws As Worksheet) As Long

    Dim lEndRow As Long

    lEndRow = ws.Cells(ws.Rows.Count, MAIN_KEYWORDS_COL).End(xlUp).Row

    getKeywordsCount = lEndRow - KEYWORDS_BEGIN_ROW + 1

End Function

Private Sub getKeywords(ByRef ws As Worksheet, ByVal lIndex As Long, ByRef ReplaceInfo As ReplaceInformation)

    Dim sReplaceWord    As String

    ReplaceInfo.sKeyword = ws.Cells(KEYWORDS_BEGIN_ROW + lIndex, MAIN_KEYWORDS_COL).Value

    ReplaceInfo.lCount = 0
    ReDim ReplaceInfo.sReplaceWords(0)

    sReplaceWord = ws.Cells(KEYWORDS_BEGIN_ROW + lIndex, REPLACE_WORDS_BEGIN_COL).Value

    Do Until sReplaceWord = ""
        ReDim Preserve ReplaceInfo.sReplaceWords(ReplaceInfo.lCount)

        ReplaceInfo.sReplaceWords(ReplaceInfo.lCount) = ws.Cells(KEYWORDS_BEGIN_ROW + lIndex, REPLACE_WORDS_BEGIN_COL + ReplaceInfo.lCount).Value
        ReplaceInfo.lCount = ReplaceInfo.lCount + 1

        sReplaceWord = ws.Cells(KEYWORDS_BEGIN_ROW + lIndex, REPLACE_WORDS_BEGIN_COL + ReplaceInfo.lCount).Value
    Loop

End Sub

Private Function getTargetRows(ByRef ws As Worksheet, ByVal sKeyword As String, ByRef lTargetRows() As Long) As Long

    Dim xlFilterRow     As Range
    Dim lHeaderRow      As Long
    Dim lEndRow         As Long
    Dim lTargetCounts   As Long
    Dim lCount          As Long

    With ws
        'オートフィルタの設定状態
        If .AutoFilterMode = True Then
            'オートフィルタが設定済みなら、一旦解除
            .AutoFilter.Range.AutoFilter
        End If

        'オートフィルタのヘッダとなるダミー行を挿入
        .Rows(1).Insert
        lHeaderRow = 1
        .Cells(lHeaderRow, TARGET_COL).Value = "DummyHeader"

        '終了行
        lEndRow = .Cells(.Rows.Count, TARGET_COL).End(xlUp).Row

        '指定文字データを抽出
        With .Range(.Cells(1, TARGET_COL), .Cells(lEndRow, TARGET_COL))
            'オートフィルタ実行
'            .AutoFilter Field:=1, Criteria1:="=*" & sKeyword & "*"
            .AutoFilter Field:=1, Criteria1:=sKeyword

            '絞り込まれた件数取得(ヘッダー行が含まれているため補正)
            lTargetCounts = .SpecialCells(xlCellTypeVisible).Count - lHeaderRow

            If lTargetCounts > 0 Then
                '対象の行を格納する配列初期化
                ReDim lTargetRows(lTargetCounts - 1)

                For Each xlFilterRow In .SpecialCells(xlVisible)
                    '対象データの行の取得
                    If xlFilterRow.Row > lHeaderRow Then
                        'ヘッダー行以降
                        lTargetRows(lCount) = xlFilterRow.Row - lHeaderRow

                        lCount = lCount + 1
                    End If
                Next xlFilterRow
            End If
        End With

        'オートフィルタ解除
        .AutoFilter.Range.AutoFilter

        'ダミーヘッダー行削除
        .Rows(1).Delete
    End With

    getTargetRows = lTargetCounts

End Function

Private Sub replaceCells(ByRef wsTarget As Worksheet, ByRef lTargetRows() As Long, ByRef ReplaceInfo As ReplaceInformation)

    Dim lBeginRow   As Long
    Dim lEndRow     As Long
    Dim lTargetIndex    As Long
    Dim lTargetCount    As Long
    Dim lReplaceIndex   As Long
    Dim i               As Long

    lTargetIndex = 0
    lReplaceIndex = 0

    Do Until (lTargetIndex > UBound(lTargetRows)) Or (lReplaceIndex > ReplaceInfo.lCount - 1)
        lBeginRow = lTargetRows(lTargetIndex)

        lEndRow = lBeginRow

        lTargetCount = 1

        '値の連続した最終行を取得
        For i = 1 To UBound(lTargetRows) - lTargetIndex
            If lTargetRows(lTargetIndex + i) = lEndRow + 1 Then
                lEndRow = lEndRow + 1

                lTargetCount = lTargetCount + 1
            Else
                Exit For
            End If
        Next i

'        '2行以上連続していたら置換
'        If lEndRow > lBeginRow Then
            With wsTarget
                '置換
'                .Range(.Cells(lBeginRow, TARGET_COL), .Cells(lEndRow, TARGET_COL)).Replace What:=ReplaceInfo.sKeyword, Replacement:=ReplaceInfo.sReplaceWords(lReplaceIndex), LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
                .Range(.Cells(lBeginRow, TARGET_COL), .Cells(lEndRow, TARGET_COL)).Value = ReplaceInfo.sReplaceWords(lReplaceIndex)
            End With

            lTargetIndex = lTargetIndex + lTargetCount

            lReplaceIndex = lReplaceIndex + 1
'        Else
'            lTargetIndex = lTargetIndex + 1
'        End If
    Loop

End Sub
2018/05/09 21:46:00
id:moon-fondu

遅くなりまして、すみません(;'∀')
ありがとうございます、うまく置換できました!
助かりました(^^;

2018/05/15 21:45:40

その他の回答(0件)

id:Z1000S No.1

Z1000S回答回数27ベストアンサー獲得回数202018/04/24 08:39:38ここでベストアンサー

ポイント1000pt

完全一致のセルが連続する場合のみを置換対象とするのであれば、
オートフィルタの指定を下記のように変更するだけで大丈夫です。

            'オートフィルタ実行
'            .AutoFilter Field:=1, Criteria1:="=*" & sKeyword & "*"
            .AutoFilter Field:=1, Criteria1:=sKeyword

りんご
りんご ジョナゴールド
りんご ふじ
りんご 紅花

の先頭の「りんご」のような
完全一致のセルが単独で存在する場合も置換対象とするのであれば、
上記の変更に加え、置換処理部のIf、Else部を下記のようにコメント化、および修正して下さい。
(2018/04/24 09:00一部修正)

'        '2行以上連続していたら置換
'        If lEndRow > lBeginRow Then
            With wsTarget
                '置換
'                .Range(.Cells(lBeginRow, TARGET_COL), .Cells(lEndRow, TARGET_COL)).Replace What:=sMainKeywords, Replacement:=sReplaceWords(lReplaceIndex), LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
                .Range(.Cells(lBeginRow, TARGET_COL), .Cells(lEndRow, TARGET_COL)).Value = sReplaceWords(lReplaceIndex)
            End With

            lTargetIndex = lTargetIndex + lTargetCount

            lReplaceIndex = lReplaceIndex + 1
'        Else
'            lTargetIndex = lTargetIndex + 1
'        End If
他8件のコメントを見る
id:Z1000S

勘違いして、先の10個固定版を元にしていました。
申し訳ないです。
これでどうでしょうか

Option Explicit

'置換対象データ関連
Private Const TARGET_SHEET_NAME     As String = "Sheet1"

'フィルターの基準となる列(G列)
Private Const TARGET_COL            As Long = 7


'キーワード関連
Private Const KEYWORDS_SHEET_NAME   As String = "Sheet2"

Private Const KEYWORDS_BEGIN_ROW    As Long = 2

Private Const MAIN_KEYWORDS_COL     As Long = 1

Private Const REPLACE_WORDS_BEGIN_COL   As Long = 2

Private Type ReplaceInformation
    sKeyword        As String
    lCount          As Long
    sReplaceWords() As String
End Type

Public Sub replaceCellsMain()

    Dim wsKeywords      As Worksheet
    Dim wsTarget        As Worksheet
    Dim lKeywordsCount  As Long
    Dim ReplaceInfo     As ReplaceInformation
    Dim lTargetRows()   As Long
    Dim lTargetRowsCount    As Long
    Dim i               As Long

    Application.ScreenUpdating = False

    Set wsTarget = ThisWorkbook.Worksheets(TARGET_SHEET_NAME)
    Set wsKeywords = ThisWorkbook.Worksheets(KEYWORDS_SHEET_NAME)

    '置換するキーワード数取得
    lKeywordsCount = getKeywordsCount(wsKeywords)

    For i = 0 To lKeywordsCount - 1
        '置換するキーワードと置換後の文字列取得
        Call getKeywords(wsKeywords, i, ReplaceInfo)

        'キーワードが含まれる行を格納する配列の初期化
        ReDim lTargetRow(0)

        'キーワードが含まれる行の取得
        lTargetRowsCount = getTargetRows(wsTarget, ReplaceInfo.sKeyword, lTargetRows)

        If lTargetRowsCount > 0 Then
            '置換
            Call replaceCells(wsTarget, lTargetRows, ReplaceInfo)
        End If

        Debug.Print ReplaceInfo.sKeyword & " Done."
    Next i

    Set wsKeywords = Nothing
    Set wsTarget = Nothing

    Application.ScreenUpdating = True

End Sub

Private Function getKeywordsCount(ByRef ws As Worksheet) As Long

    Dim lEndRow As Long

    lEndRow = ws.Cells(ws.Rows.Count, MAIN_KEYWORDS_COL).End(xlUp).Row

    getKeywordsCount = lEndRow - KEYWORDS_BEGIN_ROW + 1

End Function

Private Sub getKeywords(ByRef ws As Worksheet, ByVal lIndex As Long, ByRef ReplaceInfo As ReplaceInformation)

    Dim sReplaceWord    As String

    ReplaceInfo.sKeyword = ws.Cells(KEYWORDS_BEGIN_ROW + lIndex, MAIN_KEYWORDS_COL).Value

    ReplaceInfo.lCount = 0
    ReDim ReplaceInfo.sReplaceWords(0)

    sReplaceWord = ws.Cells(KEYWORDS_BEGIN_ROW + lIndex, REPLACE_WORDS_BEGIN_COL).Value

    Do Until sReplaceWord = ""
        ReDim Preserve ReplaceInfo.sReplaceWords(ReplaceInfo.lCount)

        ReplaceInfo.sReplaceWords(ReplaceInfo.lCount) = ws.Cells(KEYWORDS_BEGIN_ROW + lIndex, REPLACE_WORDS_BEGIN_COL + ReplaceInfo.lCount).Value
        ReplaceInfo.lCount = ReplaceInfo.lCount + 1

        sReplaceWord = ws.Cells(KEYWORDS_BEGIN_ROW + lIndex, REPLACE_WORDS_BEGIN_COL + ReplaceInfo.lCount).Value
    Loop

End Sub

Private Function getTargetRows(ByRef ws As Worksheet, ByVal sKeyword As String, ByRef lTargetRows() As Long) As Long

    Dim xlFilterRow     As Range
    Dim lHeaderRow      As Long
    Dim lEndRow         As Long
    Dim lTargetCounts   As Long
    Dim lCount          As Long

    With ws
        'オートフィルタの設定状態
        If .AutoFilterMode = True Then
            'オートフィルタが設定済みなら、一旦解除
            .AutoFilter.Range.AutoFilter
        End If

        'オートフィルタのヘッダとなるダミー行を挿入
        .Rows(1).Insert
        lHeaderRow = 1
        .Cells(lHeaderRow, TARGET_COL).Value = "DummyHeader"

        '終了行
        lEndRow = .Cells(.Rows.Count, TARGET_COL).End(xlUp).Row

        '指定文字データを抽出
        With .Range(.Cells(1, TARGET_COL), .Cells(lEndRow, TARGET_COL))
            'オートフィルタ実行
'            .AutoFilter Field:=1, Criteria1:="=*" & sKeyword & "*"
            .AutoFilter Field:=1, Criteria1:=sKeyword

            '絞り込まれた件数取得(ヘッダー行が含まれているため補正)
            lTargetCounts = .SpecialCells(xlCellTypeVisible).Count - lHeaderRow

            If lTargetCounts > 0 Then
                '対象の行を格納する配列初期化
                ReDim lTargetRows(lTargetCounts - 1)

                For Each xlFilterRow In .SpecialCells(xlVisible)
                    '対象データの行の取得
                    If xlFilterRow.Row > lHeaderRow Then
                        'ヘッダー行以降
                        lTargetRows(lCount) = xlFilterRow.Row - lHeaderRow

                        lCount = lCount + 1
                    End If
                Next xlFilterRow
            End If
        End With

        'オートフィルタ解除
        .AutoFilter.Range.AutoFilter

        'ダミーヘッダー行削除
        .Rows(1).Delete
    End With

    getTargetRows = lTargetCounts

End Function

Private Sub replaceCells(ByRef wsTarget As Worksheet, ByRef lTargetRows() As Long, ByRef ReplaceInfo As ReplaceInformation)

    Dim lBeginRow   As Long
    Dim lEndRow     As Long
    Dim lTargetIndex    As Long
    Dim lTargetCount    As Long
    Dim lReplaceIndex   As Long
    Dim i               As Long

    lTargetIndex = 0
    lReplaceIndex = 0

    Do Until (lTargetIndex > UBound(lTargetRows)) Or (lReplaceIndex > ReplaceInfo.lCount - 1)
        lBeginRow = lTargetRows(lTargetIndex)

        lEndRow = lBeginRow

        lTargetCount = 1

        '値の連続した最終行を取得
        For i = 1 To UBound(lTargetRows) - lTargetIndex
            If lTargetRows(lTargetIndex + i) = lEndRow + 1 Then
                lEndRow = lEndRow + 1

                lTargetCount = lTargetCount + 1
            Else
                Exit For
            End If
        Next i

'        '2行以上連続していたら置換
'        If lEndRow > lBeginRow Then
            With wsTarget
                '置換
'                .Range(.Cells(lBeginRow, TARGET_COL), .Cells(lEndRow, TARGET_COL)).Replace What:=ReplaceInfo.sKeyword, Replacement:=ReplaceInfo.sReplaceWords(lReplaceIndex), LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
                .Range(.Cells(lBeginRow, TARGET_COL), .Cells(lEndRow, TARGET_COL)).Value = ReplaceInfo.sReplaceWords(lReplaceIndex)
            End With

            lTargetIndex = lTargetIndex + lTargetCount

            lReplaceIndex = lReplaceIndex + 1
'        Else
'            lTargetIndex = lTargetIndex + 1
'        End If
    Loop

End Sub
2018/05/09 21:46:00
id:moon-fondu

遅くなりまして、すみません(;'∀')
ありがとうございます、うまく置換できました!
助かりました(^^;

2018/05/15 21:45:40

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません