+ Reply to Thread
Results 1 to 25 of 25

VBA Looping of Excel Macros

  1. #1
    Registered User
    Join Date
    07-20-2015
    Location
    New York
    MS-Off Ver
    2305 - Enterprise 365
    Posts
    147

    VBA Looping of Excel Macros

    Hello All,
    I would like to create a loop within a public sub using the attached workbook. Would provide some VBA coding for the following scenario? The following is not really code, just parameters:

    Please Login or Register  to view this content.

  2. #2
    Registered User
    Join Date
    07-20-2015
    Location
    New York
    MS-Off Ver
    2305 - Enterprise 365
    Posts
    147

    Re: VBA Looping of Excel Macros

    I apologize, I skipped a couple of steps. Here is the revised scenario. The attached workbook is in my previous posting. As I said this is not really coding:
    Please Login or Register  to view this content.

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Looping of Excel Macros

    Hi Andrewstupendo,
    I would take an educated guess that you may not be the Author of this Workbook or the codes in it ? - There are about 100 codes already in it , many of which are more complicated then the code you are asking for.
    _....

    ( ** I assume with this_...
    If entire rows have not already been selected, select Entire rows that are active starting with row 1...
    _....You meant
    If an entire region ( or data section ) has not already been selected, select the first data section, starting with row 1... _.....
    _....... be careful when you use the word “active” as this will tend to suggest the Selected Range
    )

    I have done a code for you that I think does something similar , if not exactly, what you asked for.

    Brief description of what the code is doing and how:

    220 Rem 1. Some initial stuff referencing Worksheet "Data Dumpster & Cruncher"

    300 Rem 2. All to do with determining which Range Region to Copy

    320 '2a(i) Determine the first region - ( ** you want if no Full region has been Selected, I think ** )

    380 '=========================Main Loop VBA Looping of Excel Macros=========
    I start looping some arbitrary amount here, - I chose 100

    370 '2a(ii) Determine if you should be selecting the first region , ( - I based it on a check if the last column selected is equal to the typical last column in a data section. – You may want to use different criteria. )

    450 '2a(iii) This jumps out of the Loop if no more data sections are present by checking the end of the last selected data section against the last row with anything in it. ( You may wish to play around with how you determine the last row – ii have given you different possibilities and explained them in the code ‘Comments)

    500 '2b Determine the next data section

    550 Rem 3 ?? A good song that I like to listen to just now http://listenonrepeat.com/watch/?v=i...I_Remember_You A classic from Sebastian Bach

    560 Rem 4 Copy The current data section

    630 5. Go to Worksheet "Time"

    640 6. Select cell "A1"

    650 7. Paste

    '670 8. Run Macro "String_of_Macros_Strung_Together"

    '690 9. Run Macro "Copy_Time_Sheet"

    720 Rem 10 10. Go and Loop back Crunch and Dump
    and poo in the middle of the road ; )

    _........................

    I have ‘ Rem ‘ed out the lines that Call the two Macros as the first one seems to crash. ( - Considering the size and complexity of this Workbook I would need a month just to figure WTF it is doing !! – so i leave that to you to sort out , or whoever wrote those codes ! )

    But I think my code is doing roughly, if not exactly, what you asked for

    Let me know please how you get on

    Alan

    Code is here:

    http://www.excelforum.com/showthread...06#post4504306
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Looping of Excel Macros

    Hi Andrew

    This Code in the attached appears to do as you require. Please note, no Worksheet existed for "SSM" so I added it. Error checking should be incorporated to deal with this.

    Please note also, you have a lot of "Stuff" going on in your Code...this Module should be rewritten for efficiency sake...String_of_Macros_Strung_Together 'Module1.

    Click the Button "Run New Jaslake Code"...let me know.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jaslake; 10-18-2016 at 06:38 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Looping of Excel Macros

    John,
    It is great when a professional adds the Profi solution. I learn the most then.
    ( I read the OP’s requirement that he wanted to check if a whole data region was highlighted ( selected ) then copy from the next one, and so only start at the first region if a section was not highlighted. ( Hence he wanted to Select, then Copy, rather than Copy Destination:= ) )
    But the main business you are doing a lot more efficient then me.

    Having a couple of solutions will help hopefully to the greater understanding
    Alan
    (_..... The code still errors at one of the codes called by
    Call String_of_Macros_Strung_Together
    ¬..... )

  6. #6
    Registered User
    Join Date
    07-20-2015
    Location
    New York
    MS-Off Ver
    2305 - Enterprise 365
    Posts
    147

    Re: VBA Looping of Excel Macros

    Hello Jaslake,
    I tried your macro and it seems to work OK, but there is a problem. The subtotals of each client are being deleted, leaving individual details of each client instead. Therefore, the clients' names are being repeated. The cause may be 1. The "split_dumpster_cruncher" macro inserts a row at cell A1. This may not be necessary. Not sure. Also, it looks like the macro is selecting the current region. However, ENTIRE rows of the current region must be selected. If one does not select entire rows of the current region, the "String of Macros strung together" will not work properly. I have attached a copy of what the end result should look like in the "Time" worksheet of the "Copy of Try This" workbook. In this attachment, I ran only the "String of Macros strung together" macro without running your macro "split dumpster cruncher". I hope this clarifies things.

    Best,
    Andrewstupendo

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Looping of Excel Macros

    Hi Andrew

    I don't see this attachment...please try again.
    I have attached a copy of what the end result should look like in the "Time" worksheet of the "Copy of Try This" workbook

  8. #8
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Looping of Excel Macros

    Hi Andrew
    I do not see any attached File in post #6. Either. Possibly you were talking about your original attached File?

    _.....

    Just to add a bit of clarity:

    Jaslake’s code
    This loops through all Current data Regions. It copies them to Worksheet “Time”. ( It does not select any range). In order for his code to work it adds temporarily a First row, as you noticed. At the end of his code he deletes this temporary row.

    My Code
    I explained in detail this in Post #3 ( Yon need to scroll back up a bit to see that )
    Briefly again: My code will work similarly to Jaslakes if no current region has been selected. If however a current data region is selected, then the code will start selecting current regions at the next one after the current selected data region. This is because from post #1 and post #2 , that is what I understood you wanted

    _......................

    Both our codes copy a current region and paste that to the “Time” Worksheet. I expect we both took “entire rows” to mean all the data rows, that is to say the parts of the rows with data in them.
    As I noted previously and as you confirmed the call of
    Sub String_of_Macros_Strung_Together()
    errors
    That is to say this macro gives a warning
    Sub AA1_find_active_cells_again()

    And this macro finally errors
    Sub AAA4_Sort()

    _ To modify a code to select the entire rows of current region Range is fairly easy, for example in my code replace this
    Please Login or Register  to view this content.
    to this
    Please Login or Register  to view this content.
    _.......................

    The full modified code
    Sub Andrewstupendo2()
    is here
    http://www.excelforum.com/showthread...04#post4510204

    This code now selects the entire rows as in this screenshot showing the case of the first data region
    AndrewEntireCurrentDataRowsSelect.jpg
    http://imgur.com/xhJWc5A

    On running the code the error is the same as with the previous codes:
    That is to say this macro gives a warning
    Sub AA1_find_active_cells_again()
    And this macro finally errors
    Sub AAA4_Sort()
    So the same problem again.

    This is the point at which that last macro errors
    Please Login or Register  to view this content.
    _.........................

    It would be helpful to know your level of VBA Excel Knowledge and if you are the Author of the many codes in the File you uploaded, or if you have some idea of what those codes should be doing. ( I asked also for some clarity in Post #3 regarding what you meant by active ?? )

    _.......

    If the following is the requirement of the codes , ( The following is my understanding an interpretation of what you asked us for ), then my latest code appears to do this.
    problem appears to lie in the other called codes.

    My interpretation of what you want: ( and code lines of approximately where my code does this:
    ' 0 In Worksheet "Data Dumpster & Cruncher" data regions are separated with a blank row
    ' The macro should do the following
    ' 270 ' 1. Go to Worksheet "Data Dumpster & Cruncher"
    ' 420 ' 2. If a data region has not already been selected, select Entire rows that for data regions starting with row 1 through the row before the next blank row (in the attached, that means rows 1 through 70).
    ' 440 ' 2a. Or if entire a data region has been selected, go to the next data region after the next blank row (in the attached, that means rows 72 through 149 if the entire rows for the first data region had been selected).
    ' 490 ' 2b. If there are no more filled rows below current selection, end macro with MsgBox "FINISHED!"
    ' Assuming we have entire rows for a data region selected according to the above criteria.
    ' 572 ' Select
    ' 590 ' 4. Copy
    ' 630 ' 5. Go to Worksheet "Time"
    ' 640 ' 6. Select cell "A1"
    '
    ' 650 ' 7. Paste
    '
    ' 670 ' 8. Run Macro "String_of_Macros_Strung_Together"
    ' 690 ' 9. Run Macro "Copy_Time_Sheet"
    ' 720 ' 10. Loop back to Step 1.


    Note,
    because I am looking at entire rows, I have needed to change the criteria for deciding if a data region has initially been selected. I mentioned in post #3 that you may wish to change that criteria. I have now chosen the criteria to be that of which the selection has a rows count of more than 10. Again you may wish to amend that.


    Alan

  9. #9
    Registered User
    Join Date
    07-20-2015
    Location
    New York
    MS-Off Ver
    2305 - Enterprise 365
    Posts
    147

    Re: VBA Looping of Excel Macros

    Here is the attachment again. Apparently, the computer wiped out the attachment before it crashed.
    Attached Files Attached Files

  10. #10
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Looping of Excel Macros

    Hi Andrew,
    Same results as before. If you run the code
    SubString_of_Macros_Strung_Together()
    on the selection as in your last uploaded file, then you get the same errors....


    Quote Originally Posted by Doc.AElstein View Post
    [......
    Sub String_of_Macros_Strung_Together()
    errors
    That is to say this macro gives a warning
    Sub AA1_find_active_cells_again()

    And this macro finally errors
    Sub AAA4_Sort().......
    I emphasize I just ran the code
    SubString_of_Macros_Strung_Together()

    The error has nothing to do with our codes.

    My latsest code makes excactly the same selection as you have given and then Calls
    SubString_of_Macros_Strung_Together()
    which then errors


    The problem has nothing to do with the looping of selecting and running of the of the macros.

    The problem is with the macro
    Sub AAA4_Sort()
    and / or
    Sub AA1_find_active_cells_again()

    or some other in that massive list of macros.

    You need to get that
    Sub String_of_Macros_Strung_Together()
    working.

    That is a totally different problem
    Surely you realise that ??
    Or

    when you run the code Sub String_of_Macros_Strung_Together() in that File you uploaded, does it not error ?? -

    Alan
    Last edited by Doc.AElstein; 10-27-2016 at 11:56 AM.

  11. #11
    Registered User
    Join Date
    07-20-2015
    Location
    New York
    MS-Off Ver
    2305 - Enterprise 365
    Posts
    147

    Re: VBA Looping of Excel Macros

    Hello Jaslake,
    Did you receive the attachment? I sent it to you a second time.

    Best,
    Andrewstupendo

  12. #12
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Looping of Excel Macros

    Hi Andrew

    I see your new attachment but I don't know what it represents. What am I to do with it?

  13. #13
    Registered User
    Join Date
    07-20-2015
    Location
    New York
    MS-Off Ver
    2305 - Enterprise 365
    Posts
    147

    Re: VBA Looping of Excel Macros

    The spreadsheet "Time" of the attachment represents what the end product should look like if the macro you sent me works correctly. You will notice that each client's name appears only once. However, the macro that was sent to me repeats the client names. It is my belief that if you edit the macro "Split Dumpster Cruncher" so that it selects ENTIRE ROWS of the current region instead of only the current region, this repetition problem will go away. I believe that because the "String of Macros Strung Together" functions correctly if one copies (Manually) ENTIRE ROWS of each region from the "Data Dumpster & Cruncher" worksheet and pastes it to the "Time Worksheet".

    Best,
    Andrewstupendo

  14. #14
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Looping of Excel Macros

    Hi Andrew

    I need to sort through your Code and try to figure out what you're doing...and why. It'll take a bit.

  15. #15
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Looping of Excel Macros

    Hi Andrew

    I "THINK" I understand what you're trying to accomplish...I "THINK"...

    You've a lot of "stuff" going on...it'll take me a bit to sort through it. Give me a bit.

  16. #16
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Looping of Excel Macros

    Quote Originally Posted by Andrewstupendo View Post
    ....the "String of Macros Strung Together" functions correctly if one copies (Manually) ENTIRE ROWS of each region from the "Data Dumpster & Cruncher" worksheet and pastes it to the "Time Worksheet".....
    Interesting. Manually copying entire rows for a data region ( for any data region, including the one you selected in your last uploaded file ) gives me exactly the same errors as before.
    I wish John luck!
    Alan

  17. #17
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Looping of Excel Macros

    Hi Andrew

    Attached are the results I get of clicking the Button "Run New Jaslake Code". The only change I made in the Code is indicated in the Code.
    Please Login or Register  to view this content.
    I've tested this in Excel 2007 and Excel 2010...it runs without error. Play with it...what's happening on your platform?

    Complete Code...
    Please Login or Register  to view this content.
    Please note also, you have a lot of "Stuff" going on in your Code...this Module should be rewritten for efficiency sake...String_of_Macros_Strung_Together 'Module1.
    Attached Files Attached Files

  18. #18
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Looping of Excel Macros

    FWIW. I still get exactly the same error ( XL2007 and XL2010 )

  19. #19
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Looping of Excel Macros

    Hi Doc

    I'll await Andrew's feed back but, as I run without error, I'd be interested in the error message and the Line of Code that throws the error.

  20. #20
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA Looping of Excel Macros

    Quote Originally Posted by jaslake View Post
    .... I'd be interested in the error message and the Line of Code that throws the error.
    Hi John,
    You caught me just before bedtime !

    The error is the same error as I have always had.. The error comes when on the Call of SubString_of_Macros_Strung_Together()

    see post #8
    http://www.excelforum.com/excel-prog...ml#post4510255
    and post # 10

    _...............................

    _.....

    Sub
    String_of_Macros_Strung_Together()
    errors


    That is to say this macro gives a warning
    Sub AA1_find_active_cells_again()
    Sh_tBlank.JPG
    http://imgur.com/pLwwxki
    Sh_tBlank.JPG

    And this macro finally errors
    Sub AAA4_Sort()
    CodeStops.jpg
    http://imgur.com/TivwA94
    CodeStops.JPG


    Good Night !
    Alan

    EDIT: I am quite mystified how you run without error. I have tried all your codes as well as just uploading your file and running on different computers and different Excel versions. But the error is always the same ??. The problem seems to lie in the called programs and not the ones we have written.
    But the called routines work for you ?? . Strange
    And the OP says he has a run of SubString_of_Macros_Strung_Together() without error ?? . Strange
    Last edited by Doc.AElstein; 10-31-2016 at 07:52 AM.

  21. #21
    Registered User
    Join Date
    07-20-2015
    Location
    New York
    MS-Off Ver
    2305 - Enterprise 365
    Posts
    147

    Re: VBA Looping of Excel Macros

    Hello Jaslake,
    Copying and pasting the entire rows of the current region helps clean out all the left over data in the "Time" worksheet from the previous copy and paste sequence. If one copies and pastes only the occupied cells from the "Data Dumpster & Cruncher", one ends up with repeating client names, which is NOT what I want. I only want to show one instance of each client per "Time" worksheet.

    I hope this clarifies it.

    Best,
    Andrewstupendo

  22. #22
    Registered User
    Join Date
    07-20-2015
    Location
    New York
    MS-Off Ver
    2305 - Enterprise 365
    Posts
    147

    Re: VBA Looping of Excel Macros

    Hello John,
    I didn't see your modification of the code dd Oct. 29 at 5:50PM. I will try it on my end. And let you know if this works.

    Best,
    Andrew

  23. #23
    Registered User
    Join Date
    07-20-2015
    Location
    New York
    MS-Off Ver
    2305 - Enterprise 365
    Posts
    147

    Re: VBA Looping of Excel Macros

    Hello John,
    Your modified Split Dumpster Cruncher code works perfectly. As for fixing, the String of Macros Strung Together, what would you suggest? Put all mini-macros for the String of Macros in Module 1? Also this workbook contains many other macros strung together. Should each strung-together macro have its own module? Would that help?

    Best,
    Andrewstupendo

  24. #24
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA Looping of Excel Macros

    Hi Andrew

    You're welcome...glad I could help. Thanks for the Rep.

    Regarding "String of Macros Strung Together" you probably should think about rewriting it...it's inefficient. I think I counted about 50 individual Macros being called in that Module 1 with many SELECTS which slows it down.

    Were I you I'd think about getting rid of those ~50 macros...make them work together...particularly those that are impacting Sheet Time.

    I have no idea what purpose this Code serves and it's called several times...
    Please Login or Register  to view this content.
    Nor this...
    Please Login or Register  to view this content.
    But then, hey, it's your Code...if it works don't change it until you know why you're changing it.
    Last edited by jaslake; 10-31-2016 at 05:48 PM.

  25. #25
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: VBA Looping of Excel Macros

    I know this thread is marked as SOLVED, and while I cannot comment on how well - or not - the codes work, I would just like to add a comment to Andrewstupendo that another member here has repeatedly responded to you on this thread, without you once even acknowledging anything that they said.

    Simple courtesy would be to at least say thanks
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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: 0
    Last Post: 06-09-2015, 09:30 PM
  2. ODD occurence with looping macros
    By mattmickle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-07-2015, 04:36 PM
  3. Excel Document with Macros or Access file with Macros
    By jcchs2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-01-2015, 01:21 PM
  4. Looping macros to add text to beginning and end of a paragraph
    By shanew in forum Word Programming / VBA / Macros
    Replies: 1
    Last Post: 04-18-2009, 05:26 AM
  5. [SOLVED] Calling macros, looping function? Can this code be written more efficiently?
    By Punsterr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-07-2005, 03:05 PM
  6. [SOLVED] Help needed - seriously (looping? macros? formulas?)
    By jarski in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-29-2005, 01:05 PM
  7. [SOLVED] Looping macros using VB code
    By accessuser1308 in forum Excel General
    Replies: 2
    Last Post: 03-09-2005, 08:06 PM

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