+ Reply to Thread
Results 1 to 10 of 10

Looping Issue

  1. #1
    Registered User
    Join Date
    04-27-2020
    Location
    UK
    MS-Off Ver
    2010
    Posts
    53

    Looping Issue

    Hi all,

    I'm currently run into some issues with my VBA loop.
    I had it working for a specific number of iterations (for x = 1 to 104), but am still learning it all so wanted to try and get it to loop until the end of the data, so I can use it more flexibly in future.
    I changed it all, thought I had it all working, went to run the full macro and it just stopped after running through once.
    X = X+2 and Y = Y+3 are there because I need it to check every second/third column, as we're working with groups of columns.
    Any ideas?

    Thanks!


    Please Login or Register  to view this content.

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Looping Issue

    Hi Valont

    I suggest uploading a sample of your file...explaining in detail what it is you are trying to achieve...
    from what I can see all you are doing is trying to insert blank columns when a specific cell in row 8 is blank?
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Registered User
    Join Date
    04-27-2020
    Location
    UK
    MS-Off Ver
    2010
    Posts
    53

    Re: Looping Issue

    I'd love to upload a sample but I'm dealing with personal data and if I removed it, the formatting would stop working properly in the macro's called in (I think).

    So the overview of the macro is as follows:

    Bit of initial formatting
    I currently have a group of 3 columns, that I format to remove one that's not needed.
    I then add a column to the other end of the group and format that.

    Do a little more formatting, add some conditional formatting, then remove another column.

    The part in bold is where I have been having the issues.
    The loop is looking at the next group of columns, and if it isn't blank, loop and go again.
    Or at least, that's the plan. Was hoping that would be able to take me to the end of the doc, and then move to the next action.

    I hope that helps?
    If it's not going to work without the sample, I'll see what I can do with the GDPR issues.

    Thanks!

    EDIT: It might be worth noting that the whole thing worked perfectly when I had the loop running on For x = 1 To 104 , it's literally just the loop that I'm struggling with.
    Last edited by Valont; 05-19-2020 at 05:49 AM.

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Looping Issue

    Ideally a sample file is needed...One can only assume what your setup is and your above code doesn't really explain your scenario...

    When deleting always start from the end and work forward...So from last column to first column...
    i would make use of a For Loop and not a Do while loop...

    if you are able to mock up a sample file then advice would be very easy...

  5. #5
    Registered User
    Join Date
    04-27-2020
    Location
    UK
    MS-Off Ver
    2010
    Posts
    53

    Re: Looping Issue

    Have thrown together the sample doc below.

    If you run LooperFullGDP on the GDP sheet, you will see what the macro is meant to do.
    If you run LooperFullRest on the FORUM sheet, it should do almost the exact same thing, but the Format Rest will also happen after, which will change the first 27 groups, and leave the remaining 77 (These would usually be deleted but for the sake of display, I have left them in for now) as in the other sheet.
    I have only attempted to change the format rest looping at this point, hence why they're both in there.

    Hopefully this makes more sense now and you can see the issue I was having.

    Thanks
    Attached Files Attached Files

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Looping Issue

    Rule no 1 in Excel....Never merge cells....They just cause havoc with VBA...
    There must be a easier way to do this...

    K, so lets start again...

    You first clear all data...
    Then delete all "Previous" columns...

    Now what...

    Questions...
    The GPP 1.2 etc no's ...are they continuous...Till what count etc etc
    the Current version dates how are they populated...

    I personally will just delete all the columns except for first group...Edit the first and then copy the first column through changing the GDP no's and dates...

  7. #7
    Registered User
    Join Date
    04-27-2020
    Location
    UK
    MS-Off Ver
    2010
    Posts
    53

    Re: Looping Issue

    I don't doubt that there's an easier way to do it, I'm still very new with VBA, I've only been working with it for about a week, so I'm pretty happy with the progress I've made up to this point.
    For now, I'm trying to make small changes, so I can at least get it to work, before I go back and try and make it easier. Baby steps!

    Each of the individual macros works, and when looped using the ForTo method, is no problem either, so I'm just trying to bridge that gap here.
    I'm open to a complete overhaul, but the looping is the biggest concern for now as I have plenty of other areas I'd like to use it in, but don't understand it at the moment.
    I hope where I'm coming from, sorry to be a pain!

    Can't really avoid merging the cells here, I don't think. I didn't design the workbook, so trying to keep it as similar in appearance as I can.
    As for your questions, the numbers aren't continuous, which is a bit of a nightmare.
    All the dates currently are just entered individually, but end goal is to populate about 12 sheets from one central page, when adding a new employee. Although even then, they will be added individually, not looked up from another document or a database.

    As you say, I'd ideally like to be able to edit one group and then copy the formatting across. But I've been working on one thing at a time which is why it's currently structured in this way.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Looping Issue

    My sincerest apologies Valont...Not good at hack jobs...I will be prepared to assist with new code for an easier streamlined setup...If that is the way you want to go...

    If not, perhaps someone else can assist taking into account the info which you have provided...Good luck...

    Still would like to know however, if you are clearing all the data anyway...what is the importance of the GDP no's and the Current version dates

  9. #9
    Registered User
    Join Date
    04-27-2020
    Location
    UK
    MS-Off Ver
    2010
    Posts
    53

    Re: Looping Issue

    As I said before, I'm open to making it more streamlined, but that doesn't take away from the fact I still want to understand why the do while loop isn't working, and how I can use it moving forward. More than anything I'm just trying to learn, and at the moment, this happens to be the current problem I'm trying to solve alongside that process.

    I suppose I hadn't really thought about it in depth, but the idea was that the changes to the headers would be made before the macro was run, so that it wouldn't have to run 104 groups each time, because the other info that will be going in will have no more than about 20 groups.
    Considering it takes time for the macro to run through 104 iterations, even though it's a lot quicker than manually doing it, seems a bit pointless considering we could run it 20 times, or even 10.

    So for example, if we take the FORUM sheet for example, it doesn't need to run through 104 iterations, as we only need the 27 TSOP's. So it would be ideal if we could get it to look across to where it first contains GDP, clear contents and formatting from there onward, and run the formatting for what hasn't been deleted.
    The GDP sheet is the base that I have to work on, for all of the different sheets to be created, of which TSOP's will be the biggest, hence the mess.

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Looping Issue

    Considering it takes time for the macro to run through 104 iterations, even though it's a lot quicker than manually doing it, seems a bit pointless considering we could run it 20 times, or even 10.
    I still suggest keeping first Group, edit this and then create the remainder...Look at attached as an example

    The code creates 100 groups with exact formatting within 1 second...
    Within this loop one can add the snippets to increment GDP no's and dates etc etc...
    Attached Files Attached Files
    Last edited by sintek; 05-19-2020 at 08:44 AM. Reason: Added updated file

+ 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. [SOLVED] VBA looping issue
    By BvDA in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-19-2018, 07:13 AM
  2. Looping Issue
    By sparkyster in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-27-2018, 03:22 PM
  3. Looping Issue
    By sparkyster in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-31-2018, 12:59 PM
  4. Odd macro looping issue
    By mattmickle in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-28-2015, 04:16 PM
  5. Looping issue
    By wani in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-30-2012, 02:23 AM
  6. Need help with looping issue
    By SDBoca in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2011, 08:18 PM
  7. Looping issue
    By davidm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-08-2005, 01:58 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