loop to find text and place text in adjoining cell

  • Thread starter Thread starter jat
  • Start date Start date
J

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
 
Hi,

You have confused me with this
after that, i recorded a code to remove all duplicate values in column A -
this works.
Click to expand...

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
 
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
 
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
 
Back
Top