CODEWITHSUNDEEP
excelvbasingle-quotes
Kiranshell
Kiranshell

Reputation: 267

Adding single quotes to a cell using VBA

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

Upvotes: 7

Views: 49094

Answers (3)

whytheq
whytheq

Reputation: 35557

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

Upvotes: 6

JimmyPena
JimmyPena

Reputation: 8754

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.

Upvotes: 4

BonyT
BonyT

Reputation: 10940

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

Upvotes: 2

Related Questions