Macro to create a looping macro to populate another worksheet.

**sungen99** · 06-18-2016, 01:34 PM

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

**mongoose36** · 06-18-2016, 02:54 PM

If I understood you correctly, there is no macro needed see attached sheet and let me know if there are any questions.
http://www.excelforum.com/attachment...1&d=1466276015

**sungen99** · 06-18-2016, 07:07 PM

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

**mongoose36** · 06-19-2016, 12:27 AM

Ok Ken, I'm trying to understand where your loop comes in. All I see here is a simple copy (K2:M2 to the next open line in col B:D).
Is this what you are looking for?
1. Start in row 42 in Settlement Prices
2. copy the date from Column A the number from column E and the number from column I to the next available line in the Outputs sheet
3. Loop through the rest of the rows in Settlement Prices until the end.

Is this correct? I know you have vlookups and such but with loops it's just as easy to pull the values directly.

**sungen99** · 06-19-2016, 08:52 AM

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!

**sungen99** · 06-19-2016, 05:22 PM

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

**jaslake** · 06-19-2016, 05:48 PM

Hi sungen99

There is a Commercial Services Sub-Forum in which you can purchase paid services but hold off on that for the moment. This does not SEEM to be that complicated.

I MAY suggest a different approach. Are we ALWAYS looking at Columns I and N of Sheet Settlement Prices...and WHY (would help to make sense to us)?

**sungen99** · 06-19-2016, 07:35 PM

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

**jaslake** · 06-19-2016, 09:34 PM

Cav's are playing...see you tomorrow...

**mongoose36** · 06-19-2016, 10:50 PM

Originally Posted by jaslake

Cav's are playing...see you tomorrow...

Congrats on the win!

**jaslake** · 06-20-2016, 04:35 PM

Hi sungen99

It seems that mongoose36 has this pretty well surrounded for you. However, since you did PM me, here's my effort. It includes a Calendar Control with which to choose the Start Date; the End Date is pulled from the Last Record in "Settlement Prices" (this appears to be what you were after).

The Code loops through each record in "Settlement Prices", from Start Date to End Date, copies the individual record date to "B4" of "Inputs" and fills "Outputs" with the row information.

Select a Start Date, Click Process.

**mongoose36** · 06-19-2016, 09:03 PM

Try this...
http://www.excelforum.com/attachment...1&d=1466384604

**sungen99** · 06-20-2016, 07:28 AM

Well this is certainly an amazing end result to say the least. The addition of the start and end calendar is something I would have only wished for.
Unfortunately for the purposes I need this for I need to ask you to change just one thing.

I am now wondering if to strip this down to its very bare element might be the best way to go.
Can you alter this code to do the following?
Love using your calendar for the start and end date. Let’s keep that.
When you click start all I need it to do is:
Take the beginning date and put paste it as a value in K2 of the Output page.
Then continue the loop for the next one and so on. When the macro completes the only thing that will be there is the last date in K2. That’s it.
I will then be able to all of my “calls to other macros” within your.

Thank you again so very much for your help.

**mongoose36** · 06-20-2016, 03:18 PM

Is this what you are looking for?
http://www.excelforum.com/attachment...1&d=1466450297

**mongoose36** · 06-20-2016, 03:22 PM

To make edits look inside the 'PricesLoop' Module...
Here is the loop that inputs the data.

For Each cel In DtRng
    Debug.Print cel.Value
    dt = cel.Value
    If dt >= StartDt And dt <= EndDt Then
        wsOutput.Cells(NextOpenRow, 1).Value = dt
        wsOutput.Cells(NextOpenRow, 2).Value = cel.Offset(0, 13).Value 'this is column 14 from Settlement Prices
        wsOutput.Cells(NextOpenRow, 3).Value = cel.Offset(0, 8).Value 'this is column 9 from Settlement Prices
        wsOutput.Range("K2").Value = EndDt
        NextOpenRow = NextOpenRow + 1
    ElseIf dt > EndDt Then
        Exit For
    End If
Next cel

**sungen99** · 06-20-2016, 08:24 PM

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.

**jaslake** · 06-20-2016, 10:54 PM

Hi sungen99

I think I ALMOST know what you're asking for...don't know for certain. I'll get back to you in the AM...late here...

**jaslake** · 06-21-2016, 11:58 AM

Hi sungen99

Replace Module 1 Code with this...

Option Explicit
Sub Rectangle1_Click()
   Dim ws           As Worksheet
   Dim ws1          As Worksheet
   Dim ws2          As Worksheet
   Dim LR           As Long
   Dim NR           As Long
   Dim StartRow     As Long
   Dim Rng          As Range
   Dim cel          As Range

   Set ws = Sheets("Settlement Prices")
   Set ws1 = Sheets("Output")
   Set ws2 = Sheets("Inputs")

   If ws2.Cells(1, "B").Value > ws2.Cells(2, "B").Value Then
      MsgBox "Start Date is Greater than End Date"
      Exit Sub
   End If


   With ws1
      .UsedRange.Offset(2, 0).ClearContents
      NR = 3
   End With

   With ws
      LR = .Range("A" & .Rows.Count).End(xlUp).Row

      Set Rng = .Range("A4:A" & LR)

      StartRow = Rng.Find(ws2.Cells(1, "B").Text, , xlValues, xlWhole, xlByRows, xlNext, False).Row
      For Each cel In .Range(.Cells(StartRow, "A"), .Cells(LR, "A"))
         Application.EnableEvents = False
         ws2.Cells(4, "B").Value = cel.Text
         ws1.Range("K2").Value = cel.Value
         ws1.Range("L2").Value = cel.Offset(0, 13).Value
         ws1.Range("M2").Value = cel.Offset(0, 8).Value
         ws1.Cells(NR, "A").Resize(1, 3).Value = ws1.Range("K2:M2").Value
         NR = NR + 1
         Application.EnableEvents = True
      Next cel
   End With
End Sub

**sungen99** · 06-21-2016, 01:31 PM

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.

**jaslake** · 06-21-2016, 02:55 PM

Hi sungen99

Would you WISH to choose the End Date? Currently the Code defaults to the last available date.

Here's the Workbook modified to allow you to choose the Start Date AND the End Date.

**jaslake** · 06-21-2016, 03:12 PM

Hi sungen99

I've annotated the Code...let me know what you do not understand.

Option Explicit
Sub Rectangle1_Click()
   Dim ws           As Worksheet
   Dim ws1          As Worksheet
   Dim ws2          As Worksheet
   Dim LR           As Long
   Dim NR           As Long
   Dim StartRow     As Long
   Dim Rng          As Range
   Dim cel          As Range

   Set ws = Sheets("Settlement Prices")
   Set ws1 = Sheets("Output")
   Set ws2 = Sheets("Inputs")

   If ws2.Cells(1, "B").Value > ws2.Cells(2, "B").Value Then
      MsgBox "Start Date is Greater than End Date"
      Exit Sub
   End If


   'Clear Output Sheet except for headers
   With ws1 'Sheets Output
      .UsedRange.Offset(2, 0).ClearContents
      NR = 3
   End With

   With ws  'Sheets Settlement Prices
      LR = .Range("A" & .Rows.Count).End(xlUp).Row

      Set Rng = .Range("A4:A" & LR)

      'Search for the Start Date in Settlement Prices Column A
      'and capture it's Row Number (StartRow)
      StartRow = Rng.Find(ws2.Cells(1, "B").Text, , xlValues, xlWhole, xlByRows, xlNext, False).Row
      For Each cel In .Range(.Cells(StartRow, "A"), .Cells(LR, "A"))
         Application.EnableEvents = False
         
         'Copy that Date (cel.text) to inputs B4
         ws2.Cells(4, "B").Value = cel.Text
         
         'Copy that Date (cel.Value) to Outputs K2
         ws1.Range("K2").Value = cel.Value
         
         'Copy that Date (cel.Value) to Outputs L2
         ws1.Range("L2").Value = cel.Offset(0, 13).Value
         
         'Copy that Date (cel.Value) to Outputs M2
         ws1.Range("M2").Value = cel.Offset(0, 8).Value
         
         'Copy Outputs Range("K2:M2").Value to the next available row
         'in Outputs Colimn A
         ws1.Cells(NR, "A").Resize(1, 3).Value = ws1.Range("K2:M2").Value
         NR = NR + 1
         Application.EnableEvents = True
         
      'Move on to the next Cell In .Range(.Cells(StartRow, "A"), .Cells(LR, "A"))
      'and repeat the process until the last row
      Next cel
   End With
End Sub

**sungen99** · 06-21-2016, 07:03 PM

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

Actually i can remove and replace

         'Copy that Date (cel.Value) to Outputs L2
         ws1.Range("L2").Value = cel.Offset(0, 13).Value
         
         'Copy that Date (cel.Value) to Outputs M2
         ws1.Range("M2").Value = cel.Offset(0, 8).Value

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!

**jaslake** · 06-21-2016, 08:09 PM

Hi sungen99

I KNOW i have been quite the pain on this one

It sometimes takes a bit to get "on the same page" when one is not seeing the entire picture. Not a pain at all. Thanks for the Rep...let me know of issues.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**jaslake** · 06-22-2016, 10:53 AM

Hi sungen99

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hybrid View

Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Re: Macro to create a looping macro to populate another worksheet.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Looping Worksheet Macro - Skip a Sheet

[SOLVED] Multiiple Worksheet Loop Macro only Looping on 1 sheet

Bookmarks

Bookmarks

Posting Permissions