MrExcel Publishing
Your One Stop for Excel Tips & Solutions

find and replace macro


Posted by yaneckc on August 15, 2001 1:27 PM

I CREATED A FIND AND REPLACE MACRO . THE ONLY PROBLEM IS THAT I WANT THE VALUE THAT IS REPLACED ALWAYS TO BE TEXT.
RIGHT NOW IF 000000109 IS IN COLUMN OF THE NEXT ROW IT OVERLAYS 109. I WANT IT TO BE TEXT WITH VALUE IN THIS CASE 000000109.

NO MATTER WHAT IS IN COLUMN A I WANT COLUMN C TO BE OVERLAYED WITH THAT VALUE AS TEXT.

BELOW IS COPY OF MACRO. CAN YOU CODE OFFSET TEXT.

Sub FindAndReplace()
Dim i As Integer, iCount As Integer
Application.ScreenUpdating = False
Cells(1, 3).Select 'Cell C1
i = WorksheetFunction.CountIf _
(Columns(3), "000-00017-3-5")

Do Until iCount = i
Columns(3).Find(What:="000-00017-3-5", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True).Activate
ActiveCell.Replace What:="000-00017-3-5", Replacement:=ActiveCell.Offset(1, -2), LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
iCount = iCount + 1
Loop
Application.ScreenUpdating = True
End Sub


Posted by YANECKC on August 15, 2001 3:29 PM