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

I searched for "SPEC. *". That means that this is left most in the cell.

By changing just the values in column G to xxxxSPEC, I don't have to worry about
finding them.

Then I reverse what I did (just in column G) after I moved stuff around.

Are you saying that it didn't work for you? If it didn't what did it do wrong?
(Not attachments, just text, please.)



Lillian wrote:

Dear Dave:

Thank you for spent a time work on this VBA, the
question I have is Replacement:="xxxxspec." and
what:="xxxxspec." maybe I did not explain clearly from my
previosu email.
Example:

If I find any cell of raw that only contains "spec.????"
(example spec. 1234) then this value need to moved to
columnG, (example spec. 1234),if you find spec. 1234 in
the cell of columnI, then move that spec.1234 to columnG,
then leave the space in columnI, if any columns has long
paragraph of centence like "Motion Awarding Bid, Spec.
2472 (Reclaimed Water)" in this paragraph showing Spec.
2472, then ignored it, do not need move Spec. 2472 to
columnG, otherwise it will break out this paragraph, just
leave all paragraph along, we are only look for any cell
that has "spec. ????".

Thanks for all the help.

Lillian

Pipeline) to Kershaw Construction Company, Inc

-----Original Message-----
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", "t
hursday", "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

.


--

Dave Peterson