Adding single quotes to a cell using VBA

Kiranshell picture Kiranshell · Jul 13, 2012 · Viewed 40.3k times · Source

I am trying to add single quotes to values in a column by using Chr(39), but I am only getting the second quote.
e.g. I want 'value' but I am getting value'

But if I use double quotes by using Chr(34) it works, and I get "value"

Sub AddQuote()
Dim myCell As Range
    For Each myCell In Selection
        If myCell.Value <> "" Then
            myCell.Value = Chr(39) & myCell.Value & Chr(39)
        End If
    Next myCell
End Sub

Answer

whytheq picture whytheq · Jul 14, 2012

just add another single quote:

Sub AddQuote()
Dim myCell As Range
    For Each myCell In Selection
        If myCell.Value <> "" Then
            myCell.Value = Chr(39) & Chr(39) & myCell.Value & Chr(39)
        End If
    Next myCell
End Sub