I would like to know if a macro can accomplish the following: I have a large
worksheet with about 600 employees, and each employee has at least 1 row for
each date of the month.

For each employee, I would like to search in Column C for one of 2 different
values (RSV, NEW). If that value is found in ANY row for that employee, it
will copy all values in column C (for that employee) to the corresponding row
in column D.

Example: Employee 123 below does have RSV in at least one of his rows. So,
all values in columns C are copied to the corresponding row in D.
Employee 456 does not have RSV or NEW in any of his rows, so all of column D
is left alone for that employee

Original:
A B C D
123 1/1 OFF aaaa
123 1/2 OFF OFF
123 1/3 RSV bbbb
123 1/4 cccc
456 1/1 aaaaa aaaa
456 1/2 bbbb bbbb

Result
A B C D
123 1/1 OFF OFF
123 1/2 OFF OFF
123 1/3 RSV RSV
123 1/4
456 1/1 aaaaa aaaa
456 1/2 bbbb bbbb

Give this macro a try (set the two Const statements to match your actual setup)...

Sub ProcessRSVs()
Dim X As Long
Dim LastRow As Long
Dim R As Range
Dim RSVs As Range
Dim FirstAddress As String

Const StartRow As Long = 2
Const SheetName As String = "Sheet1"

With Worksheets(SheetName)
Set R = .Range("C:C").Find("RSV")
If R Is Nothing Then Exit Sub
Set RSVs = R
FirstAddress = R.Address
Do
Set R = .Range("C:C").FindNext(R)
If R.Address = FirstAddress Then Exit Do
Set RSVs = Union(RSVs, R)
Loop
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = StartRow To LastRow
For Each R In RSVs
If R.Offset(, -2).Value = .Cells(X, "A").Value Then
.Cells(X, "D").Value = .Cells(X, "C").Value
End If
Next
Next
End With
End Sub

--
Rick (MVP - Excel)


"richzip" wrote in message ...
I would like to know if a macro can accomplish the following: I have a large
worksheet with about 600 employees, and each employee has at least 1 row for
each date of the month.

For each employee, I would like to search in Column C for one of 2 different
values (RSV, NEW). If that value is found in ANY row for that employee, it
will copy all values in column C (for that employee) to the corresponding row
in column D.

Example: Employee 123 below does have RSV in at least one of his rows. So,
all values in columns C are copied to the corresponding row in D.
Employee 456 does not have RSV or NEW in any of his rows, so all of column D
is left alone for that employee

Original:
A B C D
123 1/1 OFF aaaa
123 1/2 OFF OFF
123 1/3 RSV bbbb
123 1/4 cccc
456 1/1 aaaaa aaaa
456 1/2 bbbb bbbb

Result
A B C D
123 1/1 OFF OFF
123 1/2 OFF OFF
123 1/3 RSV RSV
123 1/4
456 1/1 aaaaa aaaa
456 1/2 bbbb bbbb

Thanks Rick,


This worked when I placed it into a smaller version of my worksheet where
the column numbers matched the sample I included. However, the actual
columns I am using are F (search for RSV and copy), and H (where I copy to if
RSV is found). When I changed Columns C & D to columns F & H in the macro,
it didn't work. Is there something else I need to change?



"Rick Rothstein" wrote:

Give this macro a try (set the two Const statements to match your actual setup)...

Sub ProcessRSVs()
Dim X As Long
Dim LastRow As Long
Dim R As Range
Dim RSVs As Range
Dim FirstAddress As String

Const StartRow As Long = 2
Const SheetName As String = "Sheet1"

With Worksheets(SheetName)
Set R = .Range("C:C").Find("RSV")
If R Is Nothing Then Exit Sub
Set RSVs = R
FirstAddress = R.Address
Do
Set R = .Range("C:C").FindNext(R)
If R.Address = FirstAddress Then Exit Do
Set RSVs = Union(RSVs, R)
Loop
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = StartRow To LastRow
For Each R In RSVs
If R.Offset(, -2).Value = .Cells(X, "A").Value Then
.Cells(X, "D").Value = .Cells(X, "C").Value
End If
Next
Next
End With
End Sub

--
Rick (MVP - Excel)


"richzip" wrote in message ...
I would like to know if a macro can accomplish the following: I have a large
worksheet with about 600 employees, and each employee has at least 1 row for
each date of the month.

For each employee, I would like to search in Column C for one of 2 different
values (RSV, NEW). If that value is found in ANY row for that employee, it
will copy all values in column C (for that employee) to the corresponding row
in column D.

Example: Employee 123 below does have RSV in at least one of his rows. So,
all values in columns C are copied to the corresponding row in D.
Employee 456 does not have RSV or NEW in any of his rows, so all of column D
is left alone for that employee

Original:
A B C D
123 1/1 OFF aaaa
123 1/2 OFF OFF
123 1/3 RSV bbbb
123 1/4 cccc
456 1/1 aaaaa aaaa
456 1/2 bbbb bbbb

Result
A B C D
123 1/1 OFF OFF
123 1/2 OFF OFF
123 1/3 RSV RSV
123 1/4
456 1/1 aaaaa aaaa
456 1/2 bbbb bbbb

Oh, and one more question. When it searches for RSV, I would like to look
for some (but not all) variations of that string. So, it shoudl search for
RSV1, RSV2, and RSV 3 .. but NOT CRSV.

Thank you

"Rick Rothstein" wrote:

Give this macro a try (set the two Const statements to match your actual setup)...

Sub ProcessRSVs()
Dim X As Long
Dim LastRow As Long
Dim R As Range
Dim RSVs As Range
Dim FirstAddress As String

Const StartRow As Long = 2
Const SheetName As String = "Sheet1"

With Worksheets(SheetName)
Set R = .Range("C:C").Find("RSV")
If R Is Nothing Then Exit Sub
Set RSVs = R
FirstAddress = R.Address
Do
Set R = .Range("C:C").FindNext(R)
If R.Address = FirstAddress Then Exit Do
Set RSVs = Union(RSVs, R)
Loop
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = StartRow To LastRow
For Each R In RSVs
If R.Offset(, -2).Value = .Cells(X, "A").Value Then
.Cells(X, "D").Value = .Cells(X, "C").Value
End If
Next
Next
End With
End Sub

--
Rick (MVP - Excel)


"richzip" wrote in message ...
I would like to know if a macro can accomplish the following: I have a large
worksheet with about 600 employees, and each employee has at least 1 row for
each date of the month.

For each employee, I would like to search in Column C for one of 2 different
values (RSV, NEW). If that value is found in ANY row for that employee, it
will copy all values in column C (for that employee) to the corresponding row
in column D.

Example: Employee 123 below does have RSV in at least one of his rows. So,
all values in columns C are copied to the corresponding row in D.
Employee 456 does not have RSV or NEW in any of his rows, so all of column D
is left alone for that employee

Original:
A B C D
123 1/1 OFF aaaa
123 1/2 OFF OFF
123 1/3 RSV bbbb
123 1/4 cccc
456 1/1 aaaaa aaaa
456 1/2 bbbb bbbb

Result
A B C D
123 1/1 OFF OFF
123 1/2 OFF OFF
123 1/3 RSV RSV
123 1/4
456 1/1 aaaaa aaaa
456 1/2 bbbb bbbb