Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro question
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 "Rich" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
Macro question | Excel Discussion (Misc queries) | |||
question on macro | Excel Discussion (Misc queries) | |||
macro question | Excel Programming | |||
question on macro | Excel Programming |