How to change this program to search for the value in a certain cell

Question

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.

I have found a video on YouTube by Chester Tugwell, that searches for a predetermined word that is found within the Macro script.

This is the code I have after trying to modify the macro script

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

What I am trying to achieve, I enter a word in a cell, in this case cell C3 in Sheet 1, then at the press of a button, it searches for all occurrences of that word in a predetermined column instead, then pastes that cell and the next 2 cells in the row in sheet 1.

Answer 1

Try this out:

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