Microsoft Excel - Macro to search each row for variable

Hi,

I have code listed below to check cell FE3 if UNMET to perform function. What I would like the Macro to do is search each Row for the word "UNMET" and if found perform function. I believe there are 65000 row in excel, therefore I would like an input box to determine the number of row or to search from row 1 to 65000. This one is pretty tough, but there are very talented people in this forum and I thank you in advance.  Thank you.



If InStr(1, (Range("FE3").Value), "UNMET") > 0 Then
    Range("E3").Select
    Selection.Copy
    Sheets("Result").Select
    Range("B12").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("ACW- Participant").Select
    Range("A3:D3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Result").Select
    Range("C12").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("ACW- Participant").Select
   
    End If

Newer versions of Excel have a lot more rows than that.  Look at the Sheet1.Cells.Find method for search.  It returns a range of cells that meet your search criteria.

Hello Lionell,
Are the UNMET data all in column FE?
You then copy data from row 3 on ACW- Participant into row 12 on Result.  Is the data to be copied sequentially from row 12 (eg 13, 14, 15 etc) or relative to row 3 from row 12  (eg 15, 19, 25, etc)?
Regards
Harry

Hello Lionell,
On the basis that the UNMET is determined by formula and all occur in column FE and that you want the data summarised sequentially on the Result sheet starting from row 12, the following might do what you want.
Sub Macro2()

Dim rngSelect, rngCell As Range
Dim intCount, intOffset As Integer

    Columns("FE:FE").Select
    Selection.SpecialCells(xlCellTypeFormulas, 2).Select
    Set rngSelect = Selection
    intOffset = 0
    For Each rngCell In rngSelect
      If rngCell.Value = "unmet" Then
        Sheets("result").Range("B12").Offset(intOffset, 0).Value = rngCell.Offset(0, -rngCell.Column + 5).Value
        For intCount = 0 To 3
          Sheets("result").Range("C12").Offset(intOffset, intCount).Value = rngCell.Offset(0, -rngCell.Column + intCount + 1).Value
        Next intCount
        intOffset = intOffset + 1
      End If
    Next rngCell
    
End Sub

Obviously this will need to be called from your data sheet (though it can be modified to be called from anywhere) and will need tidying up to put your selection where you want when it finishes.
Hope this helps but I also have a version that keeps the offsets in the original data.
Regards
Harry

Outstanding!!!!!!!Thanks Harry!!I did not get a chance to run it yet because I'm at a doctor's appt. but the code looks likes what I need. I will test it tonight and get back with you with the results. Thanks again!!