Need help with code debug please
 

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!

Need help with code debug please
 

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!

Need help with code debug please
 

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