Add a Single Quote
I recently had a customer call and say that he needed a way to convert a bunch of numbers to text and wanted a piece of code that he could run over and over.
After talking to him awhile, I discovered he did not know what he really wanted but he needed to convert 5,000 Social Security numbers and put a tick (') mark in front of every one of them.
This piece of code ran in about 9 seconds.
Option Explicit
Sub AddTicks()
Dim LastPlace, Z As Variant, X As Variant
Sheets("Sheet1").Select 'Chg for your sheet name
LastPlace = ActiveCell.SpecialCells(xlLastCell).Address
ActiveSheet.Range(Cells(1, 1), LastPlace).Select
Z = Selection.Address 'Get the address
For Each X In ActiveSheet.Range(Z) 'Do while
If Len(X) > 0 Then 'Find cells with something
X.FormulaR1C1 = Chr(39) & X.Text '39 is code for tick
Else
X.FormulaR1C1 = "" 'If empty do not put tick
End If
Next
End Sub
|