PDA

View Full Version : Advanced Find & Replace



pointonline
08-19-2009, 11:12 AM
How can I find & replace text isolated to a paticular column based on the first & last incidence of a paticular text string? ie: I want to add BL* to the 1st incidence of "EP1" & EL* to the last incidence of "EP1" A typical file can have EP1 through EP20 text strings & I want to do this to each incidence.

mdmackillop
08-19-2009, 11:22 AM
Welcome to VBAX,
Try this.
Sub Amend()
Dim c As Range
With ActiveSheet.Columns(5)
Set c = .Find("EL1", lookat:=xlWhole, LookIn:=xlValues, after:=.Cells(.Rows.Count), searchdirection:=xlNext)
c.Value = c.Value & "BL*"
Set c = .Find("EL1", lookat:=xlWhole, LookIn:=xlValues, after:=.Cells(.Rows.Count), searchdirection:=xlPrevious)
c.Value = c.Value & "EL*"
End With
End Sub

mdmackillop
08-19-2009, 11:25 AM
On reading again, maybe you want this for EL1 to EL20

Sub Amend2()
Dim c As Range, i As Long
With ActiveSheet.Columns(5)
For i = 1 To 20
Set c = .Find("EL" & i, lookat:=xlWhole, LookIn:=xlValues, after:=.Cells(.Rows.Count), searchdirection:=xlNext)
c.Value = c.Value & "BL*"
Set c = .Find("EL" & i, lookat:=xlWhole, LookIn:=xlValues, after:=.Cells(.Rows.Count), searchdirection:=xlPrevious)
c.Value = c.Value & "EL*"
Next
End With
End Sub