Public Sub FixASTBs()

Dim wsC As Worksheet
Dim r As Long
Dim myRange As Range

Set wsC = Sheets("Sheet1")

wsC.Activate
Range("E1").Select
Set myRange = wsC.UsedRange
r = ActiveCell.Row
If myRange("E" & r) = [*"ast"*] Then myRange("E" & r).Value = "ASTB" And
myRange("F" & r).Value = "AST"
r = r + 1
Set wsC = Nothing
End Sub

I need to find any cell in column E that contains the letters "ast" (whole
or part of cell). If this is true, I need to replace the whole cell's
contents with "ASTB", and also replace the contents of Column F with "AST"

Debug brings me to "If my Range..." line

Thanks!

Sub FixASTBs()
Dim myRange as Range, cell as Range
On Error Resume Next
With worksheets("Sheet1")
set myRange = .Range(.Cells(1,"E"),.Cells(rows.count,"E").End(xl up)) _
.SpecialCells(xlConstants,xlTextValues)
End With
On Error goto 0
if myRange is nothing then exit sub
for each cell in myRange
if Ucase(cell.Value) like "*AST*" then
cell.offset(0,1).Value = "AST"
cell.Value = "ASTB"
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"justme" wrote in message
...
Public Sub FixASTBs()

Dim wsC As Worksheet
Dim r As Long
Dim myRange As Range

Set wsC = Sheets("Sheet1")

wsC.Activate
Range("E1").Select
Set myRange = wsC.UsedRange
r = ActiveCell.Row
If myRange("E" & r) = [*"ast"*] Then myRange("E" & r).Value = "ASTB" And
myRange("F" & r).Value = "AST"
r = r + 1
Set wsC = Nothing
End Sub

I need to find any cell in column E that contains the letters "ast" (whole
or part of cell). If this is true, I need to replace the whole cell's
contents with "ASTB", and also replace the contents of Column F with "AST"

Debug brings me to "If my Range..." line

Thanks!

:) :) :) :)
Worked perfectly!
Thanks!