Macro to create a looping macro to populate another worksheet.

I am having some difficulty with how to create a macro to do the follow:

On the Inputs page you select in B1 where you want to START the process. In this case line 42 is 02/29/16. B2 simply shows how many rows of data are in the Settlement Prices workbook and adds 3 to make up for the 3 lines at the top used for labeling.

When you click on the Process button on Inputs I would like for a loop to begin starting with like 42 in this case. I want it to get the date in Col A (line 42) from Settlement Prices and place it in B4 of Inputs. This will cause F2 and G2 on the Inputs page to grab the correct data. Then I want to take that data that is found on the Output sheet and place it starting in A3 and continuing for however long the list is.

I know this seem odd, but this is just a simplified request for a much bigger process. If I can get this figured out it will automate another process.

Thank you for your help with this.

-Ken

Thank you for your help with this!

I would actually prefer to have to click a button to execute as a macro because it will be part of a much bigger program so it needs to be a macro.

Also- what I need to do with the “data” is this.

On the Output sheet K2 is the current date that’s being “worked on”. That specific data will “vlookup” the associated data in L2 and M2. I need that result to be also entered in Col C and Col D for that specific date. I had the data for the specific date all set up on the Output sheet K2:M2 because I thought that you would just copy from there and place it in the next open line in col B:D.

Does that make sense?

The purpose for this whole this is to do a “Backtest” on data. I want to be able to see what results of a math equation would be from a specific date forward. The Cells that Output L2 and M2 are referencing are Inputs F2 and G2. In this example they are just vlookuping data from Settlement Prices BUT in the real program there will be a BUNCH of math and additional macros run to get those two prices so I want to take that result for that date and do the
Thank you again-
Ken

I have taken the liberty of attaching a new file. It will simulate the results of what the after effect would be after the macro is run.
As you can see on INPUTS the starting line number for Data extraction is 112 which coincides with 6/8/16 on the SETTLEMENT PRICES Page. SO the Macro now knows to start at line 112 and continue until the last line of data is examined (in my example that number is found on INPUTS B2).
So as you can see the macro is supposed to take the data for that specific day and post it to the OUPUT Sheet.

So I was thinking the flow of this macro would go something like this:
User enters the starting line number in B1 on the INPUTS page (112 in this example) and clicks the Process Button.
The macro knows that the LOOP is going to be 6 long 112 through 117.
The macro then pastes as a number the first date (in this case 6/8/16) into INPUT B4.
When the date is entered into B4 it will auto populate F2 and G2 (which in turn will populate K2, L2, and M2 on the OUTPUT page.
The macro will then select the OUTPUT page and copy as values K2:M2 into the next open row in A:C.
The macro will then select the INPUTS page again and change the date in B4 (in this case to 6/9/16)
The macro will then repeat the process again as described above for 6/9, 6/10, 6/13, 6/14, and finally 6/15.

As you can see this is only 6 lines of data but the real program when complete will be 100’s long and the formula used in F2 and G2 on the INPUTS page will be MUCH more complex.
The problem is I am NOT a VBA programmer but rather a good HACK.. If I have the basic macro to do the above request I can ADD more details to it once I see the structure and understand how it works.
SIDE NOTE:
What WOULD be cool would be to be able to enter the DATE into B! on INPUTS and not the line number associated with the date…….


Thank you again for your help with this!

I am officially desperate here for assistance. Willing to pay for help. Just tell me how to do it.

Jaslake-
I appreciate your assistance so I will try to give you as much information as I can to help.

The answer to your question is “No” we will not always be using just those two cols.
I think the reason people are a bit confused about what I am trying to accomplish is because what I am asking for kinda makes no sense because this is only a PART of what my bigger macro is. That is because of the following reason:

This macro would actually only be the outside of a much bigger macro (actually it’s a series of macros). The reason that I have been saying to use OUTPUT K2:M2 is because there is a series of macros that will create those two numbers. F2 and G2 on INPUTS will be the actual holders of the processed data. I just mirrored the data to K2:M2 on OUTPUT because that is what I need to paste onto A:C for each specific date.
Pay no mind to the SETTLMENT PRICES for anything to do with this macro as it was just example.
To put this into prospective let me try to give you a 10,000 foot view.
Let’s call this macro I am trying to create “Backtest”.

So when you click on the “Process” button on Inputs the following happens.

BACKTEST MACRO BEGINS
Take the first date and paste it into B4.
FILL ALL COMMODITY MONTHS WITH THAT DAYS DATA MACRO BEGINS (You don’t know about this one)
END THAT DAYS MACRO
BEGING BREAKEVEN MACRO (You don’t know about this one either)
END BREAKEVEN MACRO
BEGIN OUTPUT MACRO (You don’t know about this one either)
END OUTPUT MACRO
DO NEXT BACKTEST

The reasons that I am asking for B4 to have the date selected is because that follow are looking for that information in that cell to do what they need.
Once the date is there, my other macros run, they will provide the output in K2:M2 on the OUTPUT sheet. I want it there specifically because if your creating BACKTEST to copy that data to the next open line in A:C then I can have the other macros that will be running WITHIN BACKTEST to put there math results in those cells.
So in Summary what I am looking for is a macro that will do the following:
B1 is the line of data from SETTLEMENT PRICES to start the for/next loop.
Put a note in the code ‘DO DAYS DATA MACRO
Put a note in the code ‘DO BEAKEVEN MACRO
Put a note in the code ‘DO BREAKEVEN OUTPUT MACRO
COPY and PASTE as VALUES K2:M2 on OUTPUT to next open line on OUTPUT A:C
Increase the line counter by 1 and next the next month in the list on SETTLEMENT PRICES
REPETE
END BACKTEST MACRO

Thanks you both for the assistance. Jaslake you are almost there.
Lets forget all about the vlookps for the data! and data2.
Just copy K2:M2 into A:C on the output page.
The working on date: Inputs B4 changing will get me what i need to run the call the rest of the macro to make the values of N2:M2 give me the value i need.
I dont understand your code (way to advanced for me) so can you put some note so i know the step right after you pate the new B4 (date) on Inputs please.

This is looking good. Let ask if this is working the following way as I don’t understand the code.

On the Inputs page:
Start Date using the calendar option. LOVE IT!
End Date looks like it auto populates to the last line in the Settlement Prices sheet? I tried to change it to 6/10/16 and when I ran the macro it still showed 6/15/16.
When you click the process button
Does it place the first date in cell B4 on Inputs? I need that to happen. Also at that time can you note where in the code where that happens so I know where to CALL MACRO2.

Next it will go to the Output page:
And copy K2:M2 to the next available line in A:C. (your will see I changed the data1 and data2 lines to be “0” as I just need it to copy whats in K2:M2). Once I know where to place my CALL MACRO2 IT will give the math that will be fed into K2 and M2.
I might have confused everyone but showing the data. Perhaps I should have JUST shown the dates.

This will complete what i need to be able to finish the program.

YES!!!!!! I UNDERSTAND THE CODE NOW.

Actually i can remove and replace

Please Login or Register  to view this content.

With the other macros now that the dates are being populated in B4 on the inputs page correct. My other macros will use that date to do some fancy math and then populate L2 and M2.

The rest of your code then takes L2:M2 and places it in A:C it seems so this will work. Once it does it for the first date....... my other macros will run... and then do the next date... and my macros will run.... and so on....

I think this is what i NEED!!!!!!!!!!

I KNOW i have been quite the pain on this one so I cant even begin to tell you how appreciative i am of your help...

Thank you!