So the cells that should be moved look like: Spec. ####
(and contain nothing else????)

If that's true, then I think this'll work:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim LookForStr As String
Dim FoundCell As Range
Dim lookThroughAddress As String

lookThroughAddress = "A:AE"
LookForStr = "spec. *"

For Each wks In ActiveWorkbook.Worksheets(Array("monday", "tuesday", _
"wednesday", "thursday", "Friday"))

wks.Range("G:G").Replace what:="spec.", _
Replacement:="xxxxspec.", lookat:=xlPart, MatchCase:=False

With wks.Range(lookThroughAddress)
Set FoundCell = .Cells.Find(what:=LookForStr, _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, SearchDirection:=xlNext)
If FoundCell Is Nothing Then
MsgBox LookForStr & " wasn't found in " & wks.Name
Else
Do
If FoundCell.Column = wks.Range("G:G").Column Then
'do nothing
Else
wks.Cells(FoundCell.Row, "G").Value _
= "xxxx" & FoundCell.Value
FoundCell.ClearContents
End If
Set FoundCell = .FindNext(FoundCell)
If FoundCell Is Nothing Then
Exit Do
End If
Loop
End If
End With

wks.Range("G:G").Replace what:="xxxxspec.", _
Replacement:="Spec.", lookat:=xlPart
Next wks
End Sub

It changes any Spec. in column G to xxxxSpec. That way, the macro can look for
"SPEC. *". If it finds it, it moves the value to column G (prepending xxxx
there, too).

(The xxxx stuff means that the find won't find it.)

At the end it changes xxxxSpec. back to Spec.



Lillian wrote:

I have one excel spredsheet with 5 different
worksheet "Monday", "Tuesday","Wednesday", "Thursday", "Fr
iday", each worksheet has about 2,500 records, I need to
find out on each records from ColumnA to Columns EA that
contain "spec. xxxx" then need to moved to columns G, and
leave the space there, example find "spec. xxxx" in
columnI then will be moved to columnG, then deleted
columnsI, leave space there.

Except one condition if find "spec. xxxx" within any long
paragraph will be ignored, example some of record like
this one, record #562 of Monday worksheet,on column H
has "Motion Awarding Bid, Spec. 2472 (Reclaimed Water
Pipeline) to Kershaw Construction Company, Inc." has
Sepc. 2472, just ignired, do not need to moved "Spec.
2472" to column G, on same record #562 on column N
has "spec. 2479", so only need to moved this column N to
columnG, and leave the space in column N.

Need help on this VBA, thank you so much

LIllian



--

Dave Peterson