+ Reply to Thread
Results 1 to 13 of 13

Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

  1. #1
    Registered User
    Join Date
    06-23-2014
    Location
    MidWest, USA
    MS-Off Ver
    2016
    Posts
    89

    Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    Good morning,
    My manager has tasked me with building a reporting automation piece for our biggest client. Every month, they want to have an updated units and volume count for the various loan programs they offer. 1 report will be run out of our originations system and the values from that report will need to be placed on a year to date master report that contains columns for every month and a running total. I've already built this master report template, but I'm running into difficulties setting up the code that will read the monthly originations report. It appears that I will need to use nested loop statements to make this work, and since I've only used single loop statements, I suspect my syntax is off. The code is below for just one loan program. Once I figure out the correct syntax, I can replicate for all loan programs involved. The Tname spreadsheet is the originations report and ThisWorkbook is template that the data needs to be copied into. I'm trying to use active cell properties on each spreadsheet and perhaps that won't work? I'm not sure. Any assistance will be appreciated!

    Please Login or Register  to view this content.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    I don't think anyone is going to tackle this issue without a sample workbook(s). Make sure all data is "scrubbed" so no proprietary information is revealed.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    ActiveCell, ActiveSheet and ActiveWorkbook are handy on occasion but not always the best way to go especially when you are working with multiple sheets or books. That said, I am having trouble understanding why you activate a particular window, select a sheet, activate a cell on it, and then go and activate ThisWorkbook then a sheet in it, and then a cell within that. Whatever is active last is the only active thing so the activate of Tname, Table, C2 bit seems to have no purpose?
    I also don't get the meaning of "Windows(Tname).ActivateCell = ThisWorkbook.ActivateCell"... ActivateCell is not a property or function? I imagine that line would result in an error like "Method or datamember not found"?

    I suggest you work with range variables instead of active cells, though as mentioned without a bigger context it is hard to be sure what is best or what the code is best served by.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  4. #4
    Registered User
    Join Date
    06-23-2014
    Location
    MidWest, USA
    MS-Off Ver
    2016
    Posts
    89

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    Good thoughts. I'll post the 2 spreadsheets here shortly.

  5. #5
    Registered User
    Join Date
    06-23-2014
    Location
    MidWest, USA
    MS-Off Ver
    2016
    Posts
    89

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    Here are the 2 spreadsheets. They've been stripped of identifying information, so they look a bit different. But the code should be close.

    The highlighted portions of the template spreadsheet are what the macro should be identifying and filling in with values starting in cell D14. Ideally, it will identify which month the funded report
    has been run for and then select that month from the column headers across the top and fill in those cells. Active Cell may not be the way to go about this, I'm just not sure what to use. If the Funded
    Report is the active worksheet and the Do Until condition is met, what syntax do I use to copy that cell and paste into the other spreadsheet at the correct field? So I guess the question is a two part
    one. How to use the nested loop syntax, and then how to copy and paste as per the above?
    Attached Files Attached Files

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    It looks like the loans are called one thing in one workbook and another in the other workbook. This means we'd need some sort of translation table can you provide one?

  7. #7
    Registered User
    Join Date
    06-23-2014
    Location
    MidWest, USA
    MS-Off Ver
    2016
    Posts
    89

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    Yes, I can. Just for illustrative purposes, the below should be sufficient.

    Values in the "10 YEAR FIXED SUMMARY" band should be copied and pasted into the appropriate cells for the "10 Year - Fixed"
    Values in the "10/1 LIBOR ARM SUMMARY" band should be copied and pasted into the appropriate cells for the "10/1 ARM"
    Values in the "15 YEAR FIXED SUMMARY" band should be copied and pasted into the appropriate cells for the "15 Year - Fixed"
    Values in the "20 YEAR FIXED SUMMARY" band should be copied and pasted into the appropriate cells for the "20 Year - Fixed"
    Values in the "25 YEAR FIXED SUMMARY" band should be copied and pasted into the appropriate cells for the "25 Year - Fixed"
    Values in the "30 YEAR FIXED SUMMARY" band should be copied and pasted into the appropriate cells for the "30 Year - Fixed"

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    I did a quick QA on this - it runs and the January figures look OK.

    I added a couple of sheets to the report.

    Translations - you will have to complete filling in the translation tables. I use this table in a VLOOKUP on the data page to match the names on one report with the names on the other report (see last column of data).

    Data - I decided to copy in the data from the other report rather than attempting to work across open files. The code may run faster because of this. I use the Data Table as the source for the pivot table.

    Pivot - I generate a month number based on the column number on the report sheet. I set the month number to the filter in Cell B1 on the Pivot page. The pivot table has some N/A on it because the Translation Table is not complete. Likewise the Report Sheet has a lot of empty spaces since the Translation Table is incomplete.

    Column A is the New Loan Program - that is the Loan Program name expected by the Report Sheet. I use the Find command to find the name. If the name isn't on the list, I do nothing. If the name is on the list, I get the two amounts and put them on the Report Sheet.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-23-2014
    Location
    MidWest, USA
    MS-Off Ver
    2016
    Posts
    89

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    Thanks dflak, I'll have to take some time and work through your setup here. But I like using the table as the translation and also pulling the funded report in as a table on the Data tab. One question here, you filled out the months to number 9 (September). Say this template is saved and then October is run later. How can the code flex to allow for another month's data to be added? or does the month number on the pivot page address that?

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    The months are driven by the data on the data page.
    Please Login or Register  to view this content.
    I actually attempt to read all 12 months. That's with the MonthNum = (jR/2) -1 does. It computes a month number based on what column I'm in.

    However, I do a check to see if the MonthNum actually exists in the data: If Application.CountIf(Range("Table_Data[Funding Month]"), MonthNum) > 0 Then

    If you try to set a pivot table filter to data that doesn't exist, it does bizarre things. So I make sure I have at least one piece of data that fits the filter.

    So we only have 9 months. As I go across the columns I process months 1 - 9. When I get to month 10 and later, there is no data for these months yet so the code skips processing. When month 10, 11 and 12 data are added, the code will pick them up.

  11. #11
    Registered User
    Join Date
    06-23-2014
    Location
    MidWest, USA
    MS-Off Ver
    2016
    Posts
    89

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    Thanks dflak, that's a cool use of a pivot table that I haven't seen before. It will take me a few days to walk through this and try some modifications on my side. I really appreciate you helping me out.
    Everything I've learned has come from someone else showing me something new. I'll try to pass it forward.

  12. #12
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    My philosophy is to let "basic" Excel do as much of the "heavy lifting" as possible and use VB code to stitch and bolt the pieces together in a Dr. Frankenstein fashion. Pivot tables are a very powerful tool for selecting data, consolidating it and organizing it. I also overlay the results with a named dynamic range very often and then use For Each cl in Range("RangeName") ...

    Here is some information on named dynamic ranges: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.

  13. #13
    Forum Contributor IonutC's Avatar
    Join Date
    01-28-2016
    Location
    Bucharest, Romania
    MS-Off Ver
    Office 2019
    Posts
    486

    Re: Nested Do While Loop Statements with Active Cell Properties on 2 open spreadsheets

    Thanks for sharing this Dflak!
    Please consider:
    Be polite. Thank those who have helped you.
    Click the star icon in the lower left part of the contributor's post and add Reputation. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 04-09-2013, 05:29 PM
  2. Nested For Loop + If Statements Error
    By cinarbe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-01-2012, 10:41 AM
  3. [SOLVED] replacing nested if statements with some kind of VBA loop?
    By cwyn in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-27-2012, 01:46 PM
  4. For each loop nested; getting stuck on one cell in first iteration of nested loop
    By Excel_vba in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-22-2009, 11:54 AM
  5. Combining 3 Nested If Statements Into One Cell?
    By UBSmokie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2009, 05:34 PM
  6. max value of a cell nested if statements
    By atonk in forum Excel General
    Replies: 7
    Last Post: 08-29-2008, 02:56 PM
  7. multilple nested if statements in one cell
    By Rick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-02-2006, 03:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1