Macro question


R

Rich

I have a large worksheet with about 700 employees; each employee has at least
1 row for every date of the month, and several columns. For each employee, I
would like to search Column C for 2 specific values. If those values are
found on any row for that employee, then the value in Column C should be
copied to the corresponding row in Column D for that employee (even if that
row does not contain the value searched for). Example:

The value being searched for (RSV) is found in at least one of the rows for
employee # 123. So, column C for ALL rows (even without RSV) are copied to
the corresponding column D for that employee. The value RSV is not found in
column C at all for employee 456, so column D for that employee is left alone.

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

Result:
A B C D
123 1/1 RSV RSV
123 1/2 OFF OFF
123 1/3 OFF OFF
456 1/1 OFF OFF
456 1/2 aaaa aaaa
456 1/3 bbbb bbbb
 
Ad

Advertisements

J

Joel

What was wrong with this code Iposted to your previous request?

Sub copycolumn()

OldEmployee = 0
RowCount = 1
Start = RowCount
CopyCol = False
Do While Range("A" & RowCount) <> ""
If Range("A" & RowCount) <> Range("A" & (RowCount + 1)) Then
Employee = Range("A" & RowCount)
ColARange = "A" & Start & ":A" & RowCount
ColCRange = "C" & Start & ":C" & RowCount

RSVSumProduct = "Sumproduct(--(" & ColARange & "=" & Employee & ")," & _
"--(" & ColCRange & "=""RSV""))"

RSVResult = Evaluate(RSVSumProduct)

NewSumProduct = "Sumproduct(--(" & ColARange & "=" & Employee & ")," & _
"--(" & ColCRange & "=""NEW""))"

NewResult = Evaluate(NewSumProduct)

Result = RSVResult + NewResult

If Result > 0 Then
Range(ColCRange).Copy Destination:=Range("D" & Start)
End If

Start = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro code question 1
Is this macro possible? 3
Macro questoins 2
Macro question 2
Acronym Macro 11
Acronym Macro 2 7
Deleting EXACT duplicate rows 9
variable page breaks 1

Top