| Home |
| Search |
| Today's Posts |
|
|
|
#1
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) | |||
Hybrid Mode
