Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to find text and place text in adjoining cell
i am trying to make the following a loop:
Selection.Find(What:="Employee Total", After:=ActiveCell, LookIn:= _ xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _ , MatchCase:=False, SearchFormat:=False).Activate Range("A38").Select ActiveCell.FormulaR1C1 = "=RAND()" basically, i want to find every cell where the cell text is "Employee Total" in Column B, then in the adjoining cell in column A, enter the formula "=RADN()". after that, i recorded a code to remove all duplicate values in column A - this works. after that, i need a code to loop again to do the following: for evey blank cell in the used range in Column C, copy the value in the next cell down: Col A - Column B - Column C ID NUMBER - Employee Name - blank cell =rand() - Employee Total - 75 the blank cell would copy the value 75. after than i think i can record the macro for the final clean up and stuff. any assiatance would be appreciated. thank you, jat |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to find text and place text in adjoining cell
Hi,
You have confused me with this after that, i recorded a code to remove all duplicate values in column A - this works. Column A is where we just put the =RAND() formula and if you get duplicates it's fairly easy to remove them but the removal could cause a recalculation of the worksheet and generate additional duplicates. Please clarify. Here's the code for entering the =RAND formula lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If UCase(c.Value) = "EMPLOYEE TOTAL" Then c.Offset(, -1).Formula = "=RAND()" End If Next Mike "jat" wrote: i am trying to make the following a loop: Selection.Find(What:="Employee Total", After:=ActiveCell, LookIn:= _ xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _ , MatchCase:=False, SearchFormat:=False).Activate Range("A38").Select ActiveCell.FormulaR1C1 = "=RAND()" basically, i want to find every cell where the cell text is "Employee Total" in Column B, then in the adjoining cell in column A, enter the formula "=RADN()". after that, i recorded a code to remove all duplicate values in column A - this works. after that, i need a code to loop again to do the following: for evey blank cell in the used range in Column C, copy the value in the next cell down: Col A - Column B - Column C ID NUMBER - Employee Name - blank cell =rand() - Employee Total - 75 the blank cell would copy the value 75. after than i think i can record the macro for the final clean up and stuff. any assiatance would be appreciated. thank you, jat |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to find text and place text in adjoining cell
sorry, not meant to confuse. the actaul report at times can be 60-80 pages
long, but i only care for a summary which would be 1/2 page long but cannot be generated. after the rand formula is entered, i can remove all of the extra rows because most of column A is always blank (except for the random number) thanks for your help on the first part. jat "Mike H" wrote: Hi, You have confused me with this after that, i recorded a code to remove all duplicate values in column A - this works. Column A is where we just put the =RAND() formula and if you get duplicates it's fairly easy to remove them but the removal could cause a recalculation of the worksheet and generate additional duplicates. Please clarify. Here's the code for entering the =RAND formula lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If UCase(c.Value) = "EMPLOYEE TOTAL" Then c.Offset(, -1).Formula = "=RAND()" End If Next Mike "jat" wrote: i am trying to make the following a loop: Selection.Find(What:="Employee Total", After:=ActiveCell, LookIn:= _ xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _ , MatchCase:=False, SearchFormat:=False).Activate Range("A38").Select ActiveCell.FormulaR1C1 = "=RAND()" basically, i want to find every cell where the cell text is "Employee Total" in Column B, then in the adjoining cell in column A, enter the formula "=RADN()". after that, i recorded a code to remove all duplicate values in column A - this works. after that, i need a code to loop again to do the following: for evey blank cell in the used range in Column C, copy the value in the next cell down: Col A - Column B - Column C ID NUMBER - Employee Name - blank cell =rand() - Employee Total - 75 the blank cell would copy the value 75. after than i think i can record the macro for the final clean up and stuff. any assiatance would be appreciated. thank you, jat |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to find text and place text in adjoining cell
Hi,
Here'e the code to fill column C, I'll leave the duplicate random numbers to you Sub nn() 'Populate RAND Formula lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If UCase(c.Value) = "EMPLOYEE TOTAL" Then c.Offset(, -1).Formula = "=RAND()" End If Next 'Fill column C lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 2 Step -1 If Cells(x - 1, 3).Value = "" Then Cells(x - 1, 3).Value = Cells(x, 3).Value End If Next End Sub Mike "jat" wrote: sorry, not meant to confuse. the actaul report at times can be 60-80 pages long, but i only care for a summary which would be 1/2 page long but cannot be generated. after the rand formula is entered, i can remove all of the extra rows because most of column A is always blank (except for the random number) thanks for your help on the first part. jat "Mike H" wrote: Hi, You have confused me with this after that, i recorded a code to remove all duplicate values in column A - this works. Column A is where we just put the =RAND() formula and if you get duplicates it's fairly easy to remove them but the removal could cause a recalculation of the worksheet and generate additional duplicates. Please clarify. Here's the code for entering the =RAND formula lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If UCase(c.Value) = "EMPLOYEE TOTAL" Then c.Offset(, -1).Formula = "=RAND()" End If Next Mike "jat" wrote: i am trying to make the following a loop: Selection.Find(What:="Employee Total", After:=ActiveCell, LookIn:= _ xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _ , MatchCase:=False, SearchFormat:=False).Activate Range("A38").Select ActiveCell.FormulaR1C1 = "=RAND()" basically, i want to find every cell where the cell text is "Employee Total" in Column B, then in the adjoining cell in column A, enter the formula "=RADN()". after that, i recorded a code to remove all duplicate values in column A - this works. after that, i need a code to loop again to do the following: for evey blank cell in the used range in Column C, copy the value in the next cell down: Col A - Column B - Column C ID NUMBER - Employee Name - blank cell =rand() - Employee Total - 75 the blank cell would copy the value 75. after than i think i can record the macro for the final clean up and stuff. any assiatance would be appreciated. thank you, jat |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to find text and place text in adjoining cell
thanks, prints off on 1/2 page instead of 50+ pages
jat "Mike H" wrote: Hi, Here'e the code to fill column C, I'll leave the duplicate random numbers to you Sub nn() 'Populate RAND Formula lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If UCase(c.Value) = "EMPLOYEE TOTAL" Then c.Offset(, -1).Formula = "=RAND()" End If Next 'Fill column C lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 2 Step -1 If Cells(x - 1, 3).Value = "" Then Cells(x - 1, 3).Value = Cells(x, 3).Value End If Next End Sub Mike "jat" wrote: sorry, not meant to confuse. the actaul report at times can be 60-80 pages long, but i only care for a summary which would be 1/2 page long but cannot be generated. after the rand formula is entered, i can remove all of the extra rows because most of column A is always blank (except for the random number) thanks for your help on the first part. jat "Mike H" wrote: Hi, You have confused me with this after that, i recorded a code to remove all duplicate values in column A - this works. Column A is where we just put the =RAND() formula and if you get duplicates it's fairly easy to remove them but the removal could cause a recalculation of the worksheet and generate additional duplicates. Please clarify. Here's the code for entering the =RAND formula lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set MyRange = Range("B1:B" & lastrow) For Each c In MyRange If UCase(c.Value) = "EMPLOYEE TOTAL" Then c.Offset(, -1).Formula = "=RAND()" End If Next Mike "jat" wrote: i am trying to make the following a loop: Selection.Find(What:="Employee Total", After:=ActiveCell, LookIn:= _ xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _ , MatchCase:=False, SearchFormat:=False).Activate Range("A38").Select ActiveCell.FormulaR1C1 = "=RAND()" basically, i want to find every cell where the cell text is "Employee Total" in Column B, then in the adjoining cell in column A, enter the formula "=RADN()". after that, i recorded a code to remove all duplicate values in column A - this works. after that, i need a code to loop again to do the following: for evey blank cell in the used range in Column C, copy the value in the next cell down: Col A - Column B - Column C ID NUMBER - Employee Name - blank cell =rand() - Employee Total - 75 the blank cell would copy the value 75. after than i think i can record the macro for the final clean up and stuff. any assiatance would be appreciated. thank you, jat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find text and copy selected rows from text and loop | Excel Programming | |||
Stop spilling the text to the adjoining cell | Excel Discussion (Misc queries) | |||
place text in one cell | Excel Worksheet Functions | |||
loop question trying to bring excel into autocad text not starting in correct place | Excel Programming | |||
Why am I seeing ###### in place of text within the cell? | Excel Discussion (Misc queries) |