Convert selection to SQL ANY() format


Board Regular
May 14, 2015
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)"
End Sub

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


Thank you in advance for any suggestions!

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.

141ActiveContracted Cleaning'141',
141ActiveContracted Cleaning'205',
213InactiveVehicle Depreciation'332',
332ActiveGrounds keeping'463')
141ActiveContracted Cleaning
213InactiveVehicle Depreciation
141ActiveContracted Cleaning


Here is the code for the above example:

Sub ANY_Expression()


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


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

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
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back