在excel VBA中查找包含关键字的单元格

1 回答

• 0

  使用 Range 对象的Autofilter()方法

  Sub main()
      With Worksheets("hammer") '<--| reference searched worksheet (change "hammer" to its actual name)
          With .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) '<--| reference its column "A" cells from row 1 down to last non empty one (change "A"s to your actual searched column index)
              .AutoFilter Field:=1, Criteria1:="*hammer*" '<--| filter on referenced column to get cell containing "hammer"
              If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("different tab").Range("A1") '<--| copy any filtered cell into "different tab" worksheet
          End With
          .AutoFilterMode = False '<--| show all rows back
      End With
  End Sub
  

  它假设搜索范围的第一个单元格（在此示例中为"A1"）是 Headers 单元格，因此不会搜索"*hammer" .

  回复于 2024-05-15T05:47:55+08:00