loop to find text and place text in adjoining cell

[jat]

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

 

[Mike H]

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]

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]

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]

thanks, prints off on 1/2 page instead of 50+ pages

jat