Excel VBA

css navigation by Css3Menu.com

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
                X.FormulaR1C1 = ""  'If empty do not put tick
            End If
End Sub

© 2001-2016

Updated:  11/16/2016 19:50
This page added:  01 May 2001