Excel

Add Apostrophes Before Data

Ease of Use

Easy

Version tested with

2000 

Submitted by:

Anne Troy

Description:

Adds an apostrophe before all data in a workbook; generally for exporting data to be used in other databases. 

Discussion:

You may need to add apostrophes in front of all your data for submission to some other data handling procedure such as an external database. This code will automatically add apostrophes to the left, as the first character, of all used cells in the active worksheet in the active workbook. 

Code:

instructions for use

			

Option Explicit Sub AddApostrophes() Dim cel As Range Dim strw As String strw = "You cannot undo this task." strw = strw & " You may want to save your work to a new file." strw = strw & " Press OK to proceed, Cancel to quit." If vbCancel = MsgBox(strw, vbOKCancel + vbCritical, "Task Cannot Be Undone") Then Exit Sub For Each cel In ActiveSheet.UsedRange cel.Value = "'" & cel.Value Next cel End Sub

How to use:

  1. Copy the code above.
  2. Open your workbook or your personal.xls file.
  3. Hit Alt+F11 to open the Visual Basic Editor (VBE).
  4. From the menu, choose Insert-Module.
  5. Paste the code into the code window at right. If in your personal.xls file, then rename the Sub to Public Sub PrefixApostrophies().
  6. Save the file and close the VBE.
 

Test the code:

  1. Open the file on which you want to run the code.
  2. Hit Tools-Macro-Macros and double-click AddApostrophes.
 

Sample File:

apostrophes.zip 7.22KB 

Approved by mdmackillop


This entry has been viewed 132 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express