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",

[TABLE="width: 96"]
<colgroup><col width="64" style="width: 48pt;" span="2"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Data
[/TD]
[TD="width: 64, bgcolor: transparent"]Query
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]334[/TD]
[TD="bgcolor: transparent"]'334',
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]667
[/TD]
[TD="bgcolor: transparent"]'667',
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]75757[/TD]
[TD="bgcolor: transparent"]'75757',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]35353[/TD]
[TD="bgcolor: transparent"]'35353',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]887[/TD]
[TD="bgcolor: transparent"]'887',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]254546[/TD]
[TD="bgcolor: transparent"]'254546',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6555[/TD]
[TD="bgcolor: transparent"]'6555',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4343434[/TD]
[TD="bgcolor: transparent"]'4343434',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4432323[/TD]
[TD="bgcolor: transparent"]'4432323',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3233223[/TD]
[TD="bgcolor: transparent"]'3233223',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]334[/TD]
[TD="bgcolor: transparent"]'334',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]667[/TD]
[TD="bgcolor: transparent"]'667',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]75757[/TD]
[TD="bgcolor: transparent"]'75757',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]35353[/TD]
[TD="bgcolor: transparent"]'35353',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]887[/TD]
[TD="bgcolor: transparent"]'887',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]254546[/TD]
[TD="bgcolor: transparent"]'254546',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6555[/TD]
[TD="bgcolor: transparent"]'6555',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4343434[/TD]
[TD="bgcolor: transparent"]'4343434',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4432323[/TD]
[TD="bgcolor: transparent"]'4432323',[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3233223[/TD]
[TD="bgcolor: transparent"]'3233223',

[/TD]
[/TR]
</tbody>[/TABLE]
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