Macro to Concatenate Based on Entered Date

    Hello all


    I have attached a spreadsheet illustrating my query.


    I would like to run a sub procedure called generatedataset then I'd like to enter a date from column J (starting from J4) into G12 (cell G12 is a named range called effective_date). Then enter the concatenation of the adjacent cells K4 and L4 and place this into cell G9 (cell G9 is a named range called dataset_name). Then call the procedure again. Repeating for all values in range starting at J4 extending down, and for all corresponding concatenated values in K and L.


    Data would always be taken from columns J or K&L and placed in the same cells ie G12 (named range effective_date) and G9 (named range dataset_name) respectively.


    I hope that's clear. Any help would be much appreciated.


    Al

    Re: Macro to Concatenate Based on Entered Date


    I don't undetstand your query, but you can use offset value to solve this problem.

    Re: Macro to Concatenate Based on Entered Date


    Hi,


    Your query seems pretty straightforward. To try to give you advice on how to solve your problem, can you tell me what causes the data you want to show in G9 and G12 to change? For examply, how are you telling Excel which line of data to use?


    John

    Re: Macro to Concatenate Based on Entered Date


    Hi s_u_resh and John


    I haven't actually created my own macro yet, someone else has created a macro i want to call. the macro i want to call uses the data inputted in cells g9 and g12 to create a txt file with other data in the spreadsheet. I have a list of about 300 dates that i want to make txt files for and i want to give them all separate names (hence the 1_backtesting name which is incremented).


    so after it runs once i want the next value in J ie 24/03/05 to be copied and pasted into cell g12 then i want the values in K and L to be concatenated and pasted into cell g9 so it reads 2_backtesting, then the macro will be called, create a text file and then 29/03/05 etc.


    Hope I've made this clearer the second time round!


    A

    Re: Macro to Concatenate Based on Entered Date


    Hi,


    Set up a macro called "test" and have that loops i from 1 to 300 then have the macro insert formula in G9 and G12 with the row element of the formula relating to i (or i + 4 in your case).


    The following is obtained using the Macro Recorder

    Code
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "=RC[3]"
    Range("G12").Select
    ActiveCell.FormulaR1C1 = "=R[-8]C[4] & R[-8]C[5]"


    Now all you have to do is tweak the code by inserting " & +i & " where you wish the value of i to be added to change the results for each value of i


    Now before you put the "next i" statement in, call your existing macro.


    John


    PS: You can use ranges and/or the offset function too and get more elegent solutions but the above is pretty easy to follow so you can work with it by trial and error to get it doing what you want.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login