I've been finding some close information, but nothing I'm able to put together is working. My problem is that I need to create SQL script to update pricing on our web-site on a variable # of items. Here is what I'm trying to do:

1. Find first empty column and select row 2 (below header row)
2. Gather starting data from row 1 and 2
3. Concatenate data from row 2 and fixed header names
4. Populate first empty cell with concatenated data
5. Fill concatenate formula down to first empty row (to concatenate each row data)

My excel sheet contains column headings in row 1 which are table names for the website. Rows 2 thru x are each unique data identified by a product_id in column 1.
My excel formula is:

=CONCATENATE("update products set ",$C$1,"='",C2,"',",$D$1,"='",D2,"' ",$E$1,"='",E2,"' ","where products_id='",A2,"';")
' Data runs from column C through column O and is variable for number of items (rows)

The result should look like:

update products set products_price='10.00', products_price1='11.00', products_price2='12.00' where products_id='1234';

I would like to have the userform select the reference for where the product_id is resident and the values for $C$1, C2, $D$1, D2, etc... insert them into the formula, and place the result of the concatenate formula into the first empty column. Finally the macro will copy the formula down to the last occupied row to build the SQL statement for each row on the sheet. The column headings will remain constant, but data reference will be respective to each row (ie C2, C3, C4, etc...)

Thanks in advance.
JTW

---------- Post added at 01:43 PM ---------- Previous post was at 01:38 PM ----------

The macro I currently have is below:


Sub Update_products_pricing()
'
' Update_products_pricing Macro
' Update Pricing script for products table
'

'
Sheets("Products").Select
Range("AB1").Select
ActiveCell.FormulaR1C1 = "Update Script for Products Table"
Selection.Font.Bold = True
Columns("AB:AB").EntireColumn.AutoFit
Range("AB2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""update products set"","" "",R1C3,""='"",RC[-25],""',"","" "",R1C5,""='"",RC[-23],""',"","" "",R1C6,""='"",RC[-22],""',"","" "",R1C7,""='"",RC[-21],""',"","" "",R1C8,""='"",RC[-20],""',"","" "",R1C9,""='"",RC[-19],""',"","" "",R1C10,""='"",RC[-18],""',"","" "",R1C11,""='"",RC[-17],""',"","" "",R1C12,""='"",RC[-16],""',"","" "",R1C13,""='"",RC[-15],""',"","" "",R1C14,""='"",RC[-14],""',"","" "",R1C15,""='"",RC[-13],""'"","" "",""where products_id='"",RC[-27],""';"")"
Range("AB2").Select
Selection.AutoFill Destination:=Range("AB2:AB" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("AB2:AB" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.Copy
Range("AC2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub