macro to Find Replace in Excel
 

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

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



"Nurddin" wrote in message
m...
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

Arvi Laanemets wrote:
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



"Nurddin" wrote in message
m...
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

Arvi Laanemets wrote:
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



"Nurddin" wrote in message
m...
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

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

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.)

Nurddin wrote:

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

--

Dave Peterson

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

thanks a lot Dave. u are great

merci

Arvi Laanemets wrote:
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



"Nurddin" wrote in message
m...
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