Better way than using a do until loop?

Good Morning,

In my excel sheet I have 2 columns of data, (previously exported from ms project earlier on in the macro) that I want to filter data out of.
At the moment, I have tried to create a loop which searches column B starting at "B4" until it has either; found the term "Engineering" or comes to the end of the list without finding it, at which point the loop stops.
If the term "Engineering" is found, the data in the cell 1 to the right is copied into cell "C20".

Here is the code i put together below:

Sub Filter()

    Range("B4").Select
Do Until Selection.Value = ""
If Selection.Value = "Engineering" Then
   Range("C20").Value = Selection.Offset(0, 1).Value
Else
        Selection.Offset(1, 0).Select
End If
Loop

End Sub

At the moment the code just keeps looping or gets stuck on the term engineering if it is found.
Can anyone improve upon this code or post/suggest an alternative to using such a loop? If done differently, the range to be checked/looped between is ("B4:B14").

Any help greatly appreciated.
Kind regards

Will your list always be from B4 to B14? Or are there chances that more data is added, so your list can have any number of rows?

Hi, mtt23,

Sub Filter()

Dim lngRowCounter As Long

lngRowCounter = 4
    
Do Until Cells(lngRowCounter, "B").Value = ""
  If Cells(lngRowCounter, "B").Value = "Engineering" Then
    Range("C20").Value = Cells(lngRowCounter, "C").Value
    Exit Do
  End If
  lngRowCounter = lngRowCounter + 1
Loop

End Sub

Better way? Apply the Autofilter, check if there is data available, copy first line of visible cells under the heading, end Autofilter.

Maybe check if there is a hit with what you search for by using WorksheetFunction.Countif(Range(B4:B14"), "Engineering") which will deliver zero for no hit and greater 0 for at least one hit.

Ciao,
Holger

What if you find multiple cells containing the word "Engineering", where should the value of 1 cell to the right go?

Holger's code oughta work-you could also use a for next loop

Sub Filter()

Dim lngRowCounter As Long

For lngRowCounter = 4 to 14
  If lcase(Cells(lngRowCounter, "B").Value) = "engineering" Then
    Range("C20").Value = Cells(lngRowCounter, "C").Value
    Exit For
  End If
Next lngRowCounter

End Sub

I've cracked it! I made a few alterations to how the loop is controlled/conditions:

Sub Filter()

    Range("B4").Select
Do
If Selection.Value = "1a_Eng (Mechanical)" Then
   Range("C20").Value = Selection.Offset(0, 1).Value
Else
        Selection.Offset(1, 0).Select
End If
Loop Until Selection.Value = "" Or Range("C20").Value = Selection.Offset(0, 1).Value

End Sub

It loops until a blank cell is reached at the end, or the engineering value is found in cell C20.

I reckon I can see now why the suggested codes didn't work ;-)