Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel programming using VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programming in Excel VBA or ??? | Excel Discussion (Misc queries) | |||
Or Excel Programming | Excel Worksheet Functions | |||
Excel Programming help | Excel Worksheet Functions | |||
Excel Programming in VB | Excel Programming | |||
Excel Programming in VB | Excel Programming |