|
|
|
|
|
|
|
|
|
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:
|
- Copy the code above.
- Open your workbook or your personal.xls file.
- Hit Alt+F11 to open the Visual Basic Editor (VBE).
- From the menu, choose Insert-Module.
- Paste the code into the code window at right. If in your personal.xls file, then rename the Sub to Public Sub PrefixApostrophies().
- Save the file and close the VBE.
|
|
Test the code:
|
- Open the file on which you want to run the code.
- Hit Tools-Macro-Macros and double-click AddApostrophes.
|
|
Sample File:
|
apostrophes.zip 7.22KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 137 times.
|
|
|