Hi,
I have just completed some simillar coding which finds a
value in column a and returns a value from and adjacent
column into another cell. Thing is though I can't quite
what the relationship is between the range values for
Roskill, Papakura, and Wiri. It may be that you need to
use the .cells(r,c) reference method and then use
activecell.offset(r?,0) for the same column.

Judging by the cities you must be a NZ'r. If so, and the
above doesn't help, I may be able to assist over the phone
if you like. Just reply and we'll sort out a means of
contacting each other.

Regards

DavidC
-----Original Message-----
Hello from Steved

The macro below finds " TOTAL SHIFT HOURS"
It finds the first and changes to City, then
looks for the next Range("A975").Select
and changes it to Roskill and so no until it does all 9
cities, The problem I have is that Range("A975").Select
gives a range value, next week for an example it might be
Range("A857").Select, which leads to my situation, is
their a work around so that it looks for the first
" TOTAL SHIFT HOURS" no matter where it is in
Column
A as this is the only column it will find it in

Thankyou.

Cells.Find(What:=" TOTAL SHIFT HOURS",
After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart,
SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False).Activate
ActiveCell.FormulaR1C1 = "City"
Range("A975").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.FormulaR1C1 = "Roskill"
Range("A1722").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.FormulaR1C1 = "Papakura"
Range("A1992").Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.FormulaR1C1 = "Wiri"
Range("A2679").Select
.