Purge Data-Entry Eliminate: "

[MSingh]

Hi,

I need to clean the data entered by a user such that any " is instantaneously removed.
The data is exported to a csv file & the " results in validation errors when importing into accounting software.
The particular Sheets ("Sheet1").Range("F13:G213") has existing worksheet change event.

Any ideas?

Thanking you
Mohamed

[HansV]

If you want to remove double quotes in the entire sheet, add the following line to the Worksheet_Change event procedure:

Code: Select all

  Target.Replace What:=Chr(34), Replacement:="", LookAt:=xlPart

If you only want to do this for cells in the range F13:G213, add the following instead:

Code: Select all

  If Not Intersect(Range("F13:G213"), Target) Is Nothing Then
    Intersect(Range("F13:G213"), Target).Replace _
      What:=Chr(34), Replacement:="", LookAt:=xlPart
  End If

[kpark91]

Hi, I have never undertstood what this code does..

Code: Select all

If Not Intersect (Range("F13:G213"), Target) Is Nothing Then

Is it checking if the range is not empty?

[HansV]

In the Worksheet_Change event procedure, Target is the range consisting of all cells that have changed.
Intersect(Range("F13:G213"), Target) consists of all cells within the range F13:G213 that have changed.
If there are no such cells (i.e. all cells that have changed are outside the range F13:G213), Intersect(Range("F13:G213"), Target) is the empty object Nothing.
The instruction

Code: Select all

    Intersect(Range("F13:G213"), Target).Replace _
      What:=Chr(34), Replacement:="", LookAt:=xlPart

would cause an error if the intersection is Nothing, so the code checks for this.