VBA查找并移动一个单词准确的单元格——比标题显示的更复杂

Question

我保证在来这里询问之前，我已经搜索过这个答案；

我正试图创建一个VBA来搜索列J中的单词"；致"；。如果发现，则需要将单元格移动到L列。

我正在挣扎的是，它只需要在"；致"；是独立的。我的细胞含有，例如；致Sarah"但这些需要保持原位。

有人能提出解决办法吗？非常感谢Pip

Answer 1

请参阅代码中的注释以获得解释。

Option Explicit
Public Sub Example()
' define worksheet
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

' find last used row in column J
Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row

' collect all cells with "To" in this variable
Dim FoundCells As Range

Dim iRow As Long
For iRow = 1 To LastRow  ' loop through cells in J
If ws.Cells(iRow, "J").Value = "To" Then  ' check if the cell is "To"
' if yes add the cell to FoundCells
If FoundCells Is Nothing Then  ' first cell found
Set FoundCells = ws.Cells(iRow, "J")
Else  ' all other cells found
Set FoundCells = Union(FoundCells, ws.Cells(iRow, "J"))
End If
End If
Next iRow

If Not FoundCells Is Nothing Then
' copy found "To" values 2 columns to the right
FoundCells.Offset(ColumnOffset:=2).Value = FoundCells.Value

' delete found "To" vaules (from column J)
FoundCells.Clear
Else
MsgBox "No cells with ""To"" were found."
End If
End Sub

我们循环浏览J列中所有使用的单元格，检查每个单元格是否为To，如果是，则收集FoundCells中的单元格。最后，我们将这些单元格的To值复制到右侧2列，并删除找到的单元格中的To值。