Need Macro Help -

[Houstonking]

Hello I have below Data - and my end goal is to reach the data in next column - I have already recorded the macro but I want it to run till there is data on the left hand column - in my example I have only 20 rows.

so what I am doing I just takin left data and adding it"" example - if 123 --- then "123",

Data	Query
334	'334',
667	'667',
75757	'75757',
35353	'35353',
887	'887',
254546	'254546',
6555	'6555',
4343434	'4343434',
4432323	'4432323',
3233223	'3233223',
334	'334',
667	'667',
75757	'75757',
35353	'35353',
887	'887',
254546	'254546',
6555	'6555',
4343434	'4343434',
4432323	'4432323',
3233223	'3233223',

<colgroup><col width="64" style="width: 48pt;" span="2"><tbody>
</tbody>

Sub CellQuery()
'
' CellQuery Macro
'

'
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Query"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=""'""&RC[-1]&""'""&"","""
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A20")
ActiveCell.Range("A1:A20").Select
End Sub

so how can remove the A1:A20 till it sees at on left hand for how many rows it has.

 

[kweaver]

How about:

Sub CellQuery()
'
' CellQuery Macro
'

'
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("B1") = "Query"
Range("B2:B" & LR) = "=""'""&RC[-1]&""'""&"","""
End Sub

??

 

[Houstonking]

Hello - thanks for help - this is working on the data but one issue i am facing is the relative reference has been removed. for example what if i have data in D Column - i want the macro to spit out the data then in E column. how can we fix that please?

 

[Michael M]

Is this a different request to the initial one....you asked for the data to be split from Col "A" initially.

If you wanted D to E then

Sub CellQuery()
' CellQuery Macro
Dim LR As Long
LR = Cells(Rows.Count, "D").End(xlUp).Row
Range("E1") = "Query"
Range("E2:E" & LR) = "=""'""&RC[-1]&""'""&"","""
End Sub

 

[Houstonking]

hello - sorry I am not clear - I don't want the macro to hard code the column - my data can be in any column - so what I want is if I select the cell in x column - then I want the z to be the macro out put - from your reply you made set to d.

 

[Houstonking]

Hello Any one please?

 

[kweaver]

Try this macro AFTER you have select the Data column, wherever that is.

Sub AdjCol()
Dim LR As Long
Dim Col As Integer
Dim ColLet As String
LR = Selection.Rows.Count
Col = 2 + ActiveCell.Column
ColLet = Split(Cells(1, Col).Address, "$")(1)
Cells(1, Col) = "Query"
Range(ColLet & "2:" & ColLet & LR) = "=""'""&RC[-2]&""'""&"","""
End Sub

 

[mse330]

I think you are trying to format the data in that way to use it in SQL query … If my assumption is correct, you can use the below code where you have to select the range of data you want to convert then run the VBA code & it will copy it in the next column adding the parentheses at the beginning & closing as well as copying the new range to so you could immediately paste it in SQL.

Also, you can save it as a personal macro & assign a short cut to it (I have added a shortcut of Ctrl+Q) as I have it handy anytime needed without putting the code again & again in each workbook - Check the following link for instructions : https://trumpexcel.com/personal-macro-workbook/

Sub SQLquery()
Dim Rg As Range, cell As Range, RgCnt As Long, Cnt As Long
Set Rg = Selection
RgCnt = Rg.Count
For Each cell In Rg
    Cnt = Cnt + 1
    Select Case Cnt
        Case 1: cell.Offset(, 1) = "('" & cell & "'"
        Case RgCnt: cell.Offset(, 1) = ",'" & cell & "')"
        Case Else: cell.Offset(, 1) = ",'" & cell & "'"
    End Select
Next
Rg.Offset(, 1).Copy
End Sub