Surround values with single quote

[bjcf33183]

Hi,
I created the below sub to convert a single column of values (of varying amounts) into a single, comma separated string.
Thus if Column A on "Sheet1" contains:

50
55
60
65
70
75

The procedure will convert that to a comma separated string on a new worksheet ("List1"), e.g.:
50, 55, 60, 65, 70, 75

What I'm *attempting* to do is tweak that slightly so that each value in the string is surrounded by a single quote, e.g.:
'50', '55', '60', '65', '70', '75'.

Below is the current procedure.

*NOTE.. I tried updating this portion :

outStr = outStr & "," & rng.Value

to this: (added the stuff in red)

outStr = "'" & outStr & "," & rng.Value & "'"

..but it didn't quite turn out right.

=============================================================

Sub ConvertColunmToCommaString2()
'Takes the list of items in Column A on "Sheet1" and converts that list to a single comma separated string on a worksheet called "List1".
'The purpose of this is to translate a list of items (user PK Numbers for example) into a string of values that can be used on a SQL script
'e.g.: DELETE COLUMN_1 FROM TABLE_1 WHERE COLUMN_2 IN('Value [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] ', 'Value [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] ', 'Value [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3"]#3[/URL] ', etc.)


'Declare Variables
Dim LastRow As Long
Dim TargetRange As Range
Dim Outrng As Range
Dim rng As Range
Dim ws1, ws2 As Worksheet
Dim wb As Workbook
Dim outStr As String
Dim DoesWs2Exist As Boolean


'Step 1: Add a new worksheet to paste the comma string of DB's into

Sheets.Add  
ActiveSheet.Name = "List1"



'Set Worksheet Variables
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("List1")


'Set "Last Row" value to the number of rows populated with a value in Column A (on Active Sheet)
LastRow = ws1.Cells(Rows.Count, "A").End(xlUp).row


'Set Input Range = ALL items in Column A on the Active Worksheet
Set TargetRange = ws1.Range("A1:A" & LastRow)


'Set Destination Range = Cell A1 on the second worksheet ("List1")
Set Outrng = ws2.Range("A1")


outStr = ""  'Set output string to null prior to below statement


'For each item in Target (Input) Range, aka Column A on the Active Worksheet..
For Each rng In TargetRange
    If outStr = "" Then
        outStr = rng.Value
    Else
        outStr = outStr & "," & rng.Value
    End If
Next
Outrng.Value = outStr


End Sub

 

[Joe4]

Try:

    If outStr = "" Then
        outStr = "'" & rng.Value & "'"
    Else
        outStr = outStr & ",'" & rng.Value & "'"
    End If

 

[Rick Rothstein]

Here is a UDF (user defined function), which can be called from other VB code if desired, that will take a vertical, contiguous range of cells and output the text string that you indicated you wanted...

[table="width: 500"]
[tr]
	[td]Function Apostrophed(VerticalRange As Range) As String
  Apostrophed = "'" & Join(Application.Transpose(VerticalRange), "', '") & "'"
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Apostrophed just like it was a built-in Excel function. For example,

=Apostrophed(A1:A6)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.