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:

Please Login or Register  to view this content.

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,

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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 ;-)