VBA Script Loop Help

  • Hello all:

    My VBA script is "almost" completed, but needs help correcting some errors before it works.
    The script is supposed to loop through filters in Regions!B5 (it's the column header) for data in Regions!B6:B553, then after filtering, copy Regions!A3:R3 to Temp!A1:R1 for each unique filtered value. For example, if there are 10 unique IDs in Regions!B, Temp!A1:R1 should have one row for each value, creating a dataset from A1:R1, to A10:R10.
    For whatever reason, the final output generated on RegionsSummary! is not looping through each filter to copy+Paste on the sheet, it is pasting duplicates of the first unique filter value, for as many times as there are unique values in regions!B.
    Can anyone identify the error? It's likely having to do with LR3 on RegionSummary, finding the last row. Nothing (according to the code) exists on RegionSummary, so it only applies the pasted value to row LR3+1 in column A. That doesn't change, so the loop keeps overwriting. If Dim LR3 is put outside of the loop, then ReDim inside of the loop, it might work, but I'm not sure how this could be accomplished. Here is the file link: http://s000.tinyupload.com/dow…2773662777787382231444371 I appreciate any help!

  • Re: VBA Script Loop Help

    Is this what you are trying to do?

    Filter the Table on the Regions sheet for each Highway ID and place the values in row 3 of the the Regions sheet into a Table on the Summary sheet?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: VBA Script Loop Help


    The problem with your first attempt is the macro pastes the formulas not the values.

    I have been working on this in the mean time while you cowboys have been chin wagging. That MakeList Function slows the whole file.

    I cleaned up the file to present the way I would have it. Took out the table and came up with the following.

    Goes nicely.

    Take care


  • Re: VBA Script Loop Help

    Hi Smallman,

    Forgive me -- I'm on my mac where Excel is quite buggy compared to my windows work laptop where I use Excel frequently.

    It seems your output in the summary sheet is the exact data i'm looking for. Fantastic work, thank you very much! I am very very grateful.

    I did have one question, and that may be due to my mac being buggy, but I see you used the run button which generated the data. Will I lose the functionality to sort the data through the named ranges to only have certain projects generate the data at once, rather than all be displayed at the same time? I will test in 8 hours when I have my windows computer available. It could just be I lost some functionality on my current computer.

    Thank you again!

  • Re: VBA Script Loop Help

    Try the attached.

    I have made the cells with data in Row 3 of the Regions sheet a named range ("FilterResults"). I added a button to the Summary sheet, click that to summarize all the Projects.

    Code assigned to that button is

  • Re: VBA Script Loop Help

    You are both flipping fantastic human beings! I was able to test the scripts out this morning. Great work! and Thank you!

  • Re: VBA Script Loop Help

    You're welcome.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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