Convert selection to SQL ANY() format

[Factotum]

I frequently need to convert a range of Excel data into an ANY() function format (table below) for PeopleSoft queries. Once I convert the format, I copy/paste into PeopleSoft. First column is an example of original data and the second column is the end result. Maybe I can just use custom formatting, but I haven't found the right format setting. I'm thinking of two approaches depending on the circumstance:

• Option A: convert the values to the new format in their existing location. I've been fiddling around with something like:

Sub ANY_Function_OptionA()
  Dim r As Range
  Set r = Application.Selection
  rangeValues = r.Value
  r.Value = "'" & rangeValues & "',"
End Sub

• Option B is to use a formula and offset to the next empty column to the right. Problem is, the next empty column isn't always 3 columns to the right as in the code below. Any suggestions on how best to offset to the next empty column? Or any suggestions of how to improve this code - I threw it together in a hurry and I know it's a bit ugly.

Sub ANY_Function_OptionB()
Range(Selection, Selection.End(xlDown)).Select
For Each c In Selection
     c.Offset(0, 3).FormulaR1C1 = "=CHAR(39)& c.value &CHAR(39)&CHAR(44)"
Next
End Sub

Sample Data. The last row (closing parenthesis) could be tacked on to 'Oranges') or put below the data in a new cell

Product	ANY(
Apples	'Apples',
Bananas	'Bananas',
Oranges	'Oranges'
	)

<tbody>
</tbody>

Thank you in advance for any suggestions!

 

[Factotum]

In case anyone is interested, I've come up with a solution - it's ugly and I'm sure there are better ways to do it. I realize that Select and GoTo are not good practice and I usually don't resort to them, but couldn't think how to get around it this time.

The table below shows an example of what I start with and the results after the blank column. In a nutshell, the user selects the range (Category column) and then the values are copied to a blank column to the right of the data set. Pasted values are converted to include single quotes and commas, sorted, and duplicates removed. Then "ANY( " is entered as a header and the last comma is changed to a closing parenthesis. Finally the results are copied to the clipboard so the user can paste it over to our internal system as an SQL expression.

If anyone gets really bored, I'd love suggestions on how to improve this, but it's also functional for what I need.

Original				Results
Category	Status	Description	blank	ANY(
141	Active	Contracted Cleaning		'141',
141	Active	Contracted Cleaning		'205',
205	Active	Salaries		'213',
213	Inactive	Vehicle Depreciation		'332',
332	Active	Grounds keeping		'463')
463	Active	Electricity
141	Active	Contracted Cleaning
205	Active	Salaries
213	Inactive	Vehicle Depreciation
141	Active	Contracted Cleaning

<tbody>
</tbody>

Here is the code for the above example:

Sub ANY_Expression()


Selection.Copy


Dim i As Long
With Application.WorksheetFunction
    For i = 1 To Columns.Count
        If .CountA(Cells(1, i).EntireColumn) = 0 Then
            Columns(i + 1).Rows("2").PasteSpecial
            Application.CutCopyMode = False
            GoTo 1
        End If
    Next i
End With



1:


Selection.Sort Key1:=Selection.Cells, Header:=xlNo


Dim c As Range


Dim Original, First, Last As String
First = Chr(39) & Chr(39)
Last = "',"



For Each c In Selection
    Original = c.Value
    c.Value = First & Original & Last
    Original = vbNullString
Next


First = vbNullString
Last = vbNullString


Selection.RemoveDuplicates Columns:=1, Header:=xlNo


ActiveCell.Offset(-1, 0).Value = "ANY("


ActiveCell.End(xlDown).Replace What:=",", Replacement:=")"


ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlDown)).Copy


End Sub