在所有单元格中搜索单词"Yes"的所有实例，使用新工作表中找到的每个"Yes"创建行

Question

我想浏览工作表中的表格。找到每个带有"是"的单元格(找到一个单元格时(。将"是"粘贴到 A1，当找到另一个 A2 时，依此类推...

我试图修改此代码以搜索所有单元格，而不仅仅是 A 行

Answer 1

以下代码应该会给你一个先机。

Sub Text_search() 
Dim Myrange As Range 
Set Myrange = ActiveSheet.UsedRange
For Each cell In Myrange
If InStr(1, cell.Value, "YES") > 0 Then
'do something
Else
'do something else
End If
Next
End Sub

Answer 2

进一步回答@isomericharsh，如果它是一个你正在查看的表格，那就简化了范围的定义;只需使用DataBodyRange。

如果表格"表1">

在"工作表1"上，并且结果要发布在"表2"上，那么我会这样做：

Sub Search_for_Yes()
Dim YesAmt As Long ' - Amount of yes's found
YesAmt = 0 'to start with
Dim ws1 As Worksheet
Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet
Set ws2 = Sheets("Sheet2")
'It's always safer to use specific references rather than ActiveSheet
For Each cell In ws1.ListObjects("Table1").DataBodyRange 'The data in the table excluding headings and totals
If cell.Value = "YES" Then 'might need to add wildcards to this if you want to include cells that contain yes as part of larger text string. Also note that it's case-specific.
ws2.Cells(1 + YesAmt, 1).Value = "Yes" 'so that each time a yes is found it will log it further down
YesAmt = YesAmt + 1
End If
Next
x = MsgBox(YesAmt & " values found and listed", vbOKOnly + vbInformation)
End Sub

这有帮助吗？