VBA Excel查找列中的字符串和偏移删除并重复

6ojccjat 于 2023-02-10 发布在其他

关注(0)|答案(1)|浏览(139)

我有一个工作代码，可以在特定工作表的列中查找特定字符串，偏移并清除特定单元格的内容。但是，它只清除此搜索的第一个匹配项，我希望此代码可以在所有匹配项上工作。有人能帮我在此代码周围 Package 一个Loop或FindNext吗？因为我无法这样做。请参阅下面我已经拥有的代码。Thnx

Dim SearchValue6 As String 'located B9 
Dim Action6 As Range 'clear  
SearchValue6 = Workbooks.Open("C:\Users\.......xlsm").Worksheets("Sheet1").Range("B9").Value
    
On Error Resume Next

Worksheets(2).Columns("A:A").Select
Set Action6 = Selection.Find(What:=SearchValue6, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If Action6 Is Nothing Then
    'MsgBox "No clearings made in " & ActiveWorkbook.Name

Else
Action6.Activate
ActiveCell.Offset(0, 1).Select
ActiveCell.ClearContents

End If

excel

来源：https://stackoverflow.com/questions/75308521/vba-excel-find-string-in-column-and-offset-delete-and-repeat

1条答案

按热度按时间

w3nuxt5m1#

请尝试使用下一个更新代码并发送一些反馈：

Sub FindMultipleTimes()
   Dim SearchValue6 As String 'located B9
   Dim Action6 As Range 'clear
   SearchValue6 = Workbooks.Open("C:\Users\.......xlsm").Worksheets("Sheet1").Range("B9").Value
    
   Dim ws As Worksheet: Set ws = Worksheets(2)
   Dim firstAddress As String
   Set Action6 = ws.Columns("A:A").Find(What:=SearchValue6, After:=ws.Range("A1"), LookIn:=xlFormulas2, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

   If Not Action6 Is Nothing Then
            firstAddress = Action6.address
            Do
                    Action6.Offset(0, 1).ClearContents
                    Set Action6 = ws.Columns("A:A").FindNext(Action6) 'find the next occurrence
            Loop While Action6.address <> firstAddress
   Else
        MsgBox SearchValue6 & " could not be found in column ""A:A"" of sheet " & ws.name
   End If
End Sub

我只是改编了你的代码，但你想让工作簿必要的提取SearchValue6值，打开？

赞(0）回复(0）举报 2023-02-10

我来回答

VBA Excel查找列中的字符串和偏移删除并重复

1条答案

相关问题

热门标签

最新问答