Need help creating a simple macro

[Dorn]

I need to make a macro that will go through a column of data and looks for a
specific word, when it finds that word it should copy the data from the cell
directly to the right of it and make a list.

Example (2 columns the word "employee" is in one and the name is in another)

Start with this:

Employee Name1
random stuff taking up lots of columns along the row
random stuff taking up lots of columns along the row
random stuff taking up lots of columns along the row

Employee Name2
random stuff taking up lots of columns along the row

Employee Name3
random stuff taking up lots of columns along the row
random stuff taking up lots of columns along the row

Employee Name4
random stuff taking up lots of columns along the row

Employee Name5

Employee Name6

and I want just this:

Name1
Name2
Name3
Name4
Name5
Name6

There is a varying number of rows between each employee so I don't think a
formula would work. I want the macro to look for every instance of the word
"employee" and give me just a list of names on a new sheet. Is this
possible? Could this be done with a formula instead?

 

[Don Guillett]

Have a look in vbe help for FINDNEXT. There is a good example and you can
use

 

[Gary Keramidas]

this assumes the word employee is in column a and the name is in column b
and you want the results listed in column c


Option Explicit
Sub find_employee()
Dim rng As Range
Dim lastrow As Long
Dim firstrow As Integer
Dim cell As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Set rng = Range("a1:a" & lastrow)
firstrow = 1

For Each cell In rng.Cells
If UCase(cell.Value) = "EMPLOYEE" Then
Range("c" & firstrow).Value = cell.Offset(0, 1).Value
firstrow = firstrow + 1
End If

Next cell

End Sub