如何在多个Wksheets中同时搜索字符串? [英] How can I search for a string in multiple Wksheets simultaneously?
问题描述
我大约有30张纸希望同时运行此代码.我想在所有工作表中找到"ABC"并删除其旁边的单元格的值.
我从以下位置得到错误: Set rSearch =.** range **("A1",.range("A"& rows.count).end(x1up))
>
当我在"With"语句旁边指定"Sheet1"时,它可以工作,但是我希望此代码在我的所有工作表上运行.
Sub soek()昏暗搜索范围发现范围昏暗的sign12作为字符串昏暗sheetsarray作为工作表设置sheetsarray = ActiveWorkbook.Sheets(Array("sheet1","sheet2","sheet3"))sign12 ="ABC"与sheetsarray设置rSearch = .Range("A1",.Range("A"&Rows.Count).End(xlUp))设置rFound = rSearch.Find(What:= sign12,LookIn:= xlValues)如果rFound什么都没有,那么别的rFound.Offset(0,1).ClearContents万一结束于结束子
这个问题很像:解决方案
尝试一下(上面评论的汇总;-)
Sub soek()昏暗搜索范围发现范围昏暗的sign12作为字符串昏暗的oWB作为工作簿昏暗的oWS作为工作表设置oWB = ThisWorkbooksign12 ="ABC"对于oWB.Sheets中的每个oWS使用OWS设置rSearch = .Range("A1",.Range("A"&Rows.Count).End(xlUp))设置rFound = rSearch.Find(What:= sign12,LookIn:= xlValues)如果rFound什么都没有,那么别的rFound.Offset(0,1).ClearContents万一结束于下一个OWS结束子
I have around 30 sheets that I want this code to run in at the same time. I want to find "ABC" and delete the value of the cell next to it in all my worksheets.
I get my error from: Set rSearch = .**range**("A1", .range("A" & rows.count).end(x1up))
When I have specified "Sheet1" next to the "With" statement, it works, but I want this code to run on all my sheets.
Sub soek()
Dim rSearch As Range
Dim rFound As Range
Dim sign12 As String
Dim sheetsarray As Sheets
Set sheetsarray = ActiveWorkbook.Sheets(Array("sheet1", "sheet2", "sheet3"))
sign12 = "ABC"
With sheetsarray
Set rSearch = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Set rFound = rSearch.Find(What:=sign12, LookIn:=xlValues)
If rFound Is Nothing Then
Else
rFound.Offset(0, 1).ClearContents
End If
End With
End Sub
This question is a lot like: How to search for a string in all sheets of an Excel workbook?
But in my opinion, it's a lot easier to understand how to make code run on additional sheets reading my code than the code from the link above.
Try this (compilation of the comments above ;-)
Sub soek()
Dim rSearch As Range
Dim rFound As Range
Dim sign12 As String
Dim oWB As Workbook
Dim oWS As Worksheet
Set oWB = ThisWorkbook
sign12 = "ABC"
For Each oWS In oWB.Sheets
With oWS
Set rSearch = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Set rFound = rSearch.Find(What:=sign12, LookIn:=xlValues)
If rFound Is Nothing Then
Else
rFound.Offset(0, 1).ClearContents
End If
End With
Next oWS
End Sub
这篇关于如何在多个Wksheets中同时搜索字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!