macro to Find Replace in Excel

N

Nurddin

Hi,

I need to find double quotes in an excel file (could be a selectoin of
rows) and replace them with nothing ( yeah just want to delete them)

The built in Excel function to Find and Replace all gives me error
"formula too long"

Can someone please write me a small macro code

thanks
 
A

Arvi Laanemets

Hi

I just tried with Find and Replace

Replace All " with nothing entered removes all " from sheet/range
Replace All "" with nothing entered removes all "" from sheet/range, but
entries like " or " " remain unchanged.


Arvi Laanemets
 
N

nurddin19

Arvi said:
Hi

I just tried with Find and Replace

Replace All " with nothing entered removes all " from sheet/range
Replace All "" with nothing entered removes all "" from sheet/range, but
entries like " or " " remain unchanged.


Arvi Laanemets
 
N

nurddin19

Arvi said:
Hi

I just tried with Find and Replace

Replace All " with nothing entered removes all " from sheet/range
Replace All "" with nothing entered removes all "" from sheet/range, but
entries like " or " " remain unchanged.


Arvi Laanemets
 
N

nurddin19

the error of "formula too long" comes because the field length is very
large (2000 characters)

therefore only solution is to make a vb macro to find and replace. can
you please make me small macro.
help will be greatly appreciated.

thanks
 
D

Dave Peterson

This seemed to work ok:

Option Explicit
Sub testme02()

Dim FoundCell As Range
Dim ConstCells As Range

With Worksheets("sheet1")
Set ConstCells = Nothing
On Error Resume Next
Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues)
On Error GoTo 0
If ConstCells Is Nothing Then
MsgBox "No Text Constants on this sheet!"
Exit Sub
End If

With ConstCells
'get as many as we can in one step
.Replace what:=Chr(34), replacement:=Chr(39), _
lookat:=xlPart, searchorder:=xlByRows

Do
Set FoundCell = .Cells.Find(what:=Chr(34), _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'done, get out!
Exit Do
End If

FoundCell.Value = Replace(FoundCell.Value, Chr(34), Chr(39))

Loop
End With
End With
End Sub

Chr(34) is a double quote: "
chr(39) is a single quote: '

If you're using xl2k or higher, you can change this logical line:
FoundCell.Value = Application.Substitute(FoundCell.Value, _
Chr(34), Chr(39))
to
FoundCell.Value = Replace(FoundCell.Value, Chr(34), Chr(39))

(Replace was added in xl2k.)
 
N

nurddin19

wow, this thing works.
you made my life easy.

thanks a lot Dave. u are great

merci
 
N

nurddin19

Arvi said:
Hi

I just tried with Find and Replace

Replace All " with nothing entered removes all " from sheet/range
Replace All "" with nothing entered removes all "" from sheet/range, but
entries like " or " " remain unchanged.


Arvi Laanemets
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top