Question

I will admit this here, I am unfamiliar with the VBA language.
I am trying to write a Macro for an MS Excel file that searches through one sheet, then posts the results in another sheet.

我通过Chester Tugwell找到了一个有关YouP的录像,该录像在Macro的文字中找到了预先确定的措辞。

这是我试图修改宏观文字之后的守则。

Sub SearchForWord()

Dim SearchCol As Range
Dim SearchWord As Range
Dim PasteCell As Range

Set SearchCol = Sheet2.Range("D7:D100")

For Each Status In StatusCol

  If Sheet1.Range("C4") = "" Then
    Set PasteCell = Sheet1.Range("C4")
  Else
    Set PasteCell = Sheet1.Range("C4").End(xlDown).Offset(1, 0)
  End If
    
    If Status = Sheet1.Range("C3") Then Status.Offset(1,2).Resize(1, 3).Copy PasteCell

Next Status
        
End Sub

我试图做到的是,我用一个囚室里的一段话,即Sheet 1中的C3,然后用一个顿的报章,在预定的栏目中搜索该词的所有发生情况,然后把该囚室和排在表1中的下2个囚室。

Answer 1

引证:

Option Explicit

Sub SearchForWord()

    Dim SearchCol As Range, Status As Range
    Dim PasteCell As Range, srch
    
    Set SearchCol = Sheet2.Range("D7:D100")
    
    Set PasteCell = Sheet1.Cells(rows.count, "C").End(xlUp)
    If PasteCell.row < 4 Then Set PasteCell = Sheet1.Range("C4")
    
    srch = Sheet1.Range("C3").Value  only need to read this once
    
    For Each Status In SearchCol.Cells
        If Status.Value = srch Then
            Status.Offset(1, 2).Resize(1, 3).Copy PasteCell  copy cells
            Set PasteCell = PasteCell.Offset(1)  next paste position
        End If
    Next Status
        
End Sub

Answer 2

Copy (Partially) Matching Rows (Loop Through Cells)

Sub CopyMatchingRows()

      Define constants.
    Const COPY_COLUMNS As String = "D:F"   "F:H" ???

      Source
    Dim sws As Worksheet: Set sws = Sheet2
    Dim scrg As Range: Set scrg = sws.Range("D7:D100")
    Dim srrg As Range: Set srrg = scrg.EntireRow.Columns(COPY_COLUMNS)
    Dim cCount As Long: cCount = srrg.Columns.Count
    
      Destination
    Dim dws As Worksheet: Set dws = Sheet1
    Dim dcCell As Range: Set dcCell = dws.Range("C3")
    Dim drCell As Range: Set drCell = dcCell.Offset(1)
    Dim dStr As Variant: dStr = CStr(dcCell.Value)
    
      Declare additional variables.
    Dim surg As Range, scCell As Range, sStr As Variant
    
      Combine matching cells into a range union.
    For Each scCell In scrg.Cells
        sStr = CStr(scCell.Value)
        If InStr(1, sStr, dStr, vbTextCompare) > 0 Then   contains
          or
         If StrComp(sStr, dStr, vbTextCompare) = 0 Then   is equal
            If surg Is Nothing Then
                Set surg = scCell
            Else
                Set surg = Union(surg, scCell)
            End If
        End If
    Next scCell
    
      Check the range union (No matches?).
    If surg Is Nothing Then
        MsgBox "The word """ & dStr & """ was not found in range """ _
            & scrg.Address(0, 0) & """ of sheet """ & sws.Name & """!", _
            vbExclamation
        Exit Sub
    End If
                
      Copy.
    Intersect(surg.EntireRow, srg).Copy drCell
    
      Clear below.
    Dim drCount As Long: drCount = surg.Cells.Count
    drCell.Resize(dws.Rows.Count - drCell.Row - drCount + 1, cCount) _
        .Offset(drCount).Clear
        
      Inform.
    MsgBox "Rows containing """ & dStr & """ copied from sheet """ _
        & sws.Name & """ to sheet """ & dws.Name & """.", vbInformation
        
End Sub

Copy (Partially) Matching Rows (Loop Through Cells)

友情链接