Adding single quotes to a cell using VBA

Question

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

Valency

so close! - I'll add my solution – Elder

Answer 1

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

Elder

Answer 2

I prefer this syntax. Instead of looping through each cell, just read the whole selection into an array, act on the array, then dump the array contents back onto the worksheet. A maximum of two touches of the worksheet.

Sub AddQuote()

  Dim i As Long, j As Long
  Dim inputData As Variant
  Dim outputData As Variant

  ' only act on a range
  If TypeName(Selection) = "Range" Then
    ' assign selection value to a Variant array
    inputData = Selection.Value
    ' create an output array of the same size
    ReDim outputData(LBound(inputData) To UBound(inputData), _
                     LBound(inputData, 2) To UBound(inputData, 2))

    ' loop through all array dimensions
    For i = LBound(inputData) To UBound(inputData)
      For j = LBound(inputData, 2) To UBound(inputData, 2)
        ' array element will be = Empty if cell was empty
        If Not IsEmpty(inputData(i, j)) Then
          outputData(i, j) = Chr(39) & Chr(39) & inputData(i, j) & Chr(39)
        End If
      Next j
    Next i

    Selection.Value = outputData
  End If

End Sub

It also accounts for a multiple-column selection.

Heteropterous

Answer 3

The first quote ' means the cell is treated as text.

So you'll have to use two single quotes on the LHS, and one on the right for it to appear correctly.

note if A1 = ''test' and A2 = ''abc'

Then =A1&A2 will give you 'test''abc' as you'd expect

Ridings

+1 for suggesting the correct method, although you might consider adding sample code for the OP to use/adapt. – Heteropterous