在两个相同的单元格单词之间替换单元格中的单词 - Excel

Question

我在不同的单元格中写了一张纸，里面装满了不同的单词。
前任：
字1 字2 字3 字1 字4
字1 字2 字1 字4

我需要一个公式/宏来替换两个相同给定单词之间的单词，即

单词 1 单词

2 单词 3 单词 1 单词 4 替换为
字1 字1 字1 字1 字4


（对于上述内容，我们将Word2，Word3替换为整行中的Word1）

单词

1 单词 2 单词 1 单词 3 替换为
字1 字1 字1 字4

如果您需要更多详细信息，请告诉我。

谢谢！

Answer 1

我在您提供的两种情况下基于下面的代码，即如果在 word1 之间有 word2 更改它，如果在 word1 之间有 word2 和 word3（我假设它们按该顺序出现，并且总是在 word1 之间的两个单元格中）更改它们。

由于缺乏信息，该方法有点粗糙，但我知道出于保密目的，您可能无法分享更多。

Private Sub sub1()
Dim rng As Range
Dim word1 As String, word2 As String, word3 As String
Dim word4 As String, word5 As String, word6 As String
Set rng = Application.InputBox("Input the range you want to substitute", "Input", Type:=8)
word1 = "WordInCell1"
word2 = "WordInCell2"
word3 = "WordInCell3"
word4 = "WordInCell4"
word5 = "WordInCell5"
word6 = "WordInCell6"
For Each c In rng
    With c
        If .Value = word1 Then
            'Check for first case
            If .Offset(0, 2).Value = word1 Then
                'Check if the word in between the two words1 is word2
                If .Offset(0, 1).Value = word2 Then
                    .Offset(0, 1).Value = word1
                End If
            'Check for second case
            ElseIf .Offset(0, 3).Value = word1 Then
                'Check if the two words in between are word2 and word3 - assuming they always come in that order
                If .Offset(0, 1).Value = word2 And .Offset(0, 2).Value = word3 Then
                    .Offset(0, 1).Value = word1
                    .Offset(0, 2).Value = word1
                End If
            Else
                'Do Nothing
            End If
        End If
    End With
Next c
End Sub

Answer 2

这是针对您的问题的更通用的解决方案，无需对要查找的字符串进行硬编码。

Private Sub replaceWords()
    Dim rng, currentRow As Range
    Dim currentCol, firstCol, lastCol As Integer
    Dim word1 As Variant
    Dim i, j As Integer
    Set rng = Application.InputBox("Input the range you want to substitute", "Input", Type:=8)
    'Disect the range into column numbers
    firstCol = rng.Column
    lastCol = rng.Cells.SpecialCells(xlCellTypeLastCell).Column
    'Iterate through all rows
    For Each currentRow In rng.Rows
        currentCol = firstCol 'start at the left side of the range
        While currentCol < lastCol
            'Set word1 to the value of the cell in the currenct column
            word1 = Cells(currentRow.Row, currentCol).Value
            'Check subsequent columns for the same cell
            For i = currentCol + 1 To lastCol
                If Cells(currentRow.Row, i).Value = word1 Then
                    'If same word1 is found, fill cells in between with word1
                        For j = currentCol + 1 To i - 1
                            Cells(currentRow.Row, j) = word1
                        Next j
                    'Continue search at the column where word1 was last encountered position
                    currentCol = i - 1 '-1 becaus we add 1 again at the end of the while loop
                    Exit For
                End If
            Next i
            currentCol = currentCol + 1 'go to next column
        Wend
    Next
End Sub

这将从左到右工作，因此"Word1 Word2 Word1 Word2"将变为"Word1 Word1

Word1 Word2"而不是"Word1 Word2 Word2 Word2"。如果你想要相反的方式，调整代码应该不会太难。