Excel VBA: Loop and finding multiple values in a range then copying the offset cells in the same range in a categorized list in another worksheet

Question

I am in need of help with looping through a range (In this case Column "C") and finding 5 specific words (Sort, Set, Shine, Standardize, and Sustain) and then offsetting one row below the found value and copying that value to a categorized list on another sheet. To add complexity eliminating duplicate comments and blanks if possible would be great!

This is an imported form to my workbook and i am trying to create a quickly generated list of each comment under each specific word so that i can then copy into a report.

This code is borrowed and i am in need to adding multiple strings to find and be listed under each category.

Code:`Sub Find_Range() '

Dim strFind     As String
Dim oRng        As Range
Dim fRng        As Range
Dim i           As Long


strFind = "SET IN ORDER" ' string to find
Set oRng = Worksheets("IMPORTED DATA").Columns(3) ' column to search

Set fRng = oRng.Cells(oRng.Cells.Count)
For i = 1 To Application.CountIf(oRng, strFind & "*")
    Set fRng = oRng.Cells.Find(What:=strFind, _
                               LookIn:=xlValues, _
                               LookAt:=xlPart, _
                               After:=fRng, _
                               MatchCase:=False)
    If Not fRng Is Nothing Then
        With Worksheets("5S COMMENTS")
            .Cells(i, "A") = fRng.Offset(1, 0).Value2
            '.Cells(i, "B") = fRng.Offset(2, 1).Value2
        End With
    End If
Next i

' End Sub`

Any help would be greatly appreciated!!!

Picture of excel list

Answer 1

Instead of VBA I suggest to use a formula:

=LET(
  Source; 'Imported data'!C1:C999;
  Step; 3;
  FirstRow; INDEX(ROW(Source);1;0);
  Criteria; FILTER(Source; MOD(ROW(Source)-FirstRow; Step) = 0);
  Data; FILTER(Source; MOD(ROW(Source)-FirstRow; Step) = 1);
  FILTER(Data; Criteria = INDIRECT("R[-1]C";FALSE)))

Here:

• Source is a range with data to filter, where the first cell should be the one to check for the specific words
• Step is a data chunk size, which is 3 in your case I guess
• Criteria is cells to check for specific words
• Data is the Criteria shifted one row down
• INDIRECT("R[-1]C", False) addresses a cell right above the formula
• Put the formula under the word to search for