MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I NEED HELP ON MODIFING A MACRO FOR FIND/REPLACE.


Posted by YANECKC on August 16, 2001 3:22 PM

WHAT I NEED IS I HAVE COLUMN A WHICH HAS NUMBERS IN IT BUT WHAT EVER IS IN COLUMN A I WANT IT TO OVERLAY TEXT NOT NUMBERS.

COLUMNS A & C BOTH SHOULD BE TEXT.

BELOW IS A COPY OF MY MACRO

Sub FindAndReplace()

THANKS YANECK


Follow Ups:


Posted by Robb on August 17, 2001 6:29 AM

Yaneck

I don't think that method will work if you want text. As long as both columns are formatted as text, try this:

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.Offset(1, -2).Copy Destination:=ActiveCell

iCount = iCount + 1
Loop
Application.ScreenUpdating = True
End Sub

This should retain the text format.

Does it help?

Regards

Posted by YANECKC on August 17, 2001 7:21 AM

I don't think that method will work if you want text. As long as both columns are formatted as text, try this: Sub FindAndReplace() ActiveCell.Offset(1, -2).Copy Destination:=ActiveCell iCount = iCount + 1 Loop Application.ScreenUpdating = True End Sub This should retain the text format. Does it help? Regards

ROBB

THAT WAS EXACTLY WHAT I WAS LOOKING FOR. GREAT JOB!

THANK YOU FOR ALL YOUR HELP.

YANECKC : WHAT I NEED IS I HAVE COLUMN A WHICH HAS NUMBERS IN IT BUT WHAT EVER IS IN COLUMN A I WANT IT TO OVERLAY TEXT NOT NUMBERS. : COLUMNS A & C BOTH SHOULD BE TEXT. : BELOW IS A COPY OF MY MACRO : Sub FindAndReplace()