asked on

VBA macro (Excel) to remove inverted commas (x'xxx) in selected column

Good afternoon
In the many columns of my worksheet some cells are filled with pasted data in the form X'XXX.00; the other cells' formulas refer to this data. Due to the inverted commas, error #Value is all over the sheet.
I'm looking for a macro (VBA) which I can use after data has been pasted and which erases the inverted commas without affecting the formulas (which hold inverted commas, too). X'XXX.00 data should then look like XXXX.
I still use Excel 2003 on Win XP + Win 7.
Any help will be very much appreciated.
Best regards
Karl
15-06-03---test-file.xls

nutsch

Try

cells.Replace What:=Chr(39), Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Open in new window

Anne Troy

That's only a small part of the problem, however, you can just Find and Replace. Select column CCC and hit Ctrl+H and type a ' in the Find What box and don't put anything in the replace with box. Hit Replace all.

CandidKarlito

ASKER

Thanks Nutsch and Dreamboat!

Nutsch: Problem is, that this macro erases the inverted comma " ' " in the formulas as well (which refer to a 3rd value on another sheet); I would like to replace the inverted comma only in the cells' input values.

Dreamboat: the simple replace function didn't work:
Ctrl + C = highlighting column -> Alt+E,E = Find and Replace -> Alt+T = Options -> Search by Columns -->
"Look in": "Values" doesn't work, and even if I select "Formulas" I get an error message / "Update values" pop-up, respectively.
The macro solution would be preferable - I have many of these sheets, pressing once a button is quicker...

Tks for your attention in this.
Karl

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CandidKarlito

ASKER

Thank you very much!