Need Macro Suggestion for my sheet

[Mukul Gupta]

Hi All,

I have to get one Column to be update to based on some Columns. As every time for running query we do manual work in column E from E2-E28 ( in attached sheet), and every time column lenght may vary.

date is taken from Colum A, B , C and D .

We want a macro so that we can have update sheet in column E for all Row in table..

Such that:-
In attached sheet for E2, I want this below line show be automatically updated for all table values:- ( C2 and D2 are Absolute reference)

INSERT INTO VMRCKAM2.VMRRLITERAL_MSTR1 VALUES('D2','A2','B2','C2');
or in other way to understand
INSERT INTO VMRCKAM2.VMRRLITERAL_MSTR1 VALUES('COL4-ROW2','COL1','COL2','COL3-ROW2');


This need to be updated automatically through macro for all E rows upto where data in Table available.

request you to pl help me in that

 

[chirayu]

Don't need a macro. Use formula.

 

[Mukul Gupta]

HI Chirayu ,

Thanks for your input!

We use this Colunn E line for Query for our data base, So some time may happen that person formatted formula by mistake , so it can effect other purpose.

So In this case if i select cells and click macro, So It can be update and save me making mistakes (Not impacted by formula)...

Whats your input on that? Pl suggest

 

[Mukul Gupta]

Ok , Chirayu ..

Thanks, I hope this is enough,As of now

 

[chirayu]

The formula shouldn't affect the upload unless someone edits it instead of dragging down. You can always value paste before upload. But Please note that formula uses all four columns so if a column is blank then output will need to be edited. Used same formula in below macro

Sub Consolidator()

Range("E2").Select

Do Until IsEmpty(Range("A" & ActiveCell.Row))

    ActiveCell.Formula = _
    "=""INSERT INTO VMRCKAM2.VMRRLITERAL_MSTR1 VALUES(""" & _
    "&""'""&$D" & ActiveCell.Row & "&""', '""&TRIM($A" & ActiveCell.Row & _
    ")&""', '""&$B" & ActiveCell.Row & "&""', '""&$C" & ActiveCell.Row & _
    "&""');"""
    Selection.Copy
    Selection.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    ActiveCell.Offset(1, 0).Select


Loop

End Sub

 

[Mukul Gupta]

Thanks