+ Reply to Thread
Results 1 to 5 of 5

How to Stop a Do While Loop at a certain row??

Hybrid View

  1. #1
    Registered User
    Join Date
    08-21-2013
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    44

    How to Stop a Do While Loop at a certain row??

    I need to stop my Do While loop at cell 52. Cell 53 has information that I don't want to include. My starting row is 8. I need the loop to 'do while" column A equals anything other than blank "" from row 8 through row 52, but after 52 I need it to move to my next worksheet. Here is a sample of my code:

    row = 8 'starting row for worksheet-1
                Do While ActiveWorkbook.Sheets("WorkSheet-1").Cells(row, 1).Value <> ""
                        With ThisWorkbook.Sheets("TAGSTEMPLATE")
                                .Range("TAGN" & PAGETAGNUM).Value = ActiveWorkbook.Sheets("WorkSheet-1").Cells(row, 2).Value
                                .Range("EQPN" & PAGETAGNUM).Value = equipment
                                .Range("DATE" & PAGETAGNUM).Value = printdate
                                .Range("ISOT" & PAGETAGNUM).Value = ActiveWorkbook.Sheets("WorkSheet-1").Cells(row, 1).Value
                                .Range("TASK" & PAGETAGNUM).Value = task
                                .Range("ISOL" & PAGETAGNUM).Value = ActiveWorkbook.Sheets("WorkSheet-1").Cells(row, 5).Value
                                .Range("VALP" & PAGETAGNUM).Value = ActiveWorkbook.Sheets("WorkSheet-1").Cells(row, 8).Value
                        End With
                        PAGETAGNUM = PAGETAGNUM + 1
                        If PAGETAGNUM > 6 Then
                            Call printandclear
                            PAGETAGNUM = 1
                        End If
                    row = row + 1
                Loop
                
            row = 8 'starting row for worksheet-2
                Do While ActiveWorkbook.Sheets("WorkSheet-2").Cells(row, 1).Value <> ""
                        With ThisWorkbook.Sheets("TAGSTEMPLATE")
                                .Range("TAGN" & PAGETAGNUM).Value = ActiveWorkbook.Sheets("WorkSheet-2").Cells(row, 2).Value
                                .Range("EQPN" & PAGETAGNUM).Value = equipment
                                .Range("DATE" & PAGETAGNUM).Value = printdate
                                .Range("ISOT" & PAGETAGNUM).Value = ActiveWorkbook.Sheets("WorkSheet-2").Cells(row, 1).Value
                                .Range("TASK" & PAGETAGNUM).Value = task
                                .Range("ISOL" & PAGETAGNUM).Value = ActiveWorkbook.Sheets("WorkSheet-2").Cells(row, 5).Value
                                .Range("VALP" & PAGETAGNUM).Value = ActiveWorkbook.Sheets("WorkSheet-2").Cells(row, 8).Value
                        End With
                        PAGETAGNUM = PAGETAGNUM + 1
                        If PAGETAGNUM > 6 Then
                            Call printandclear
                            PAGETAGNUM = 1
                        End If
                    row = row + 1
                Loop

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

    Re: How to Stop a Do While Loop at a certain row??

    by adding second stop condition like:
                Do While ActiveWorkbook.Sheets("WorkSheet-1").Cells(row, 1).Value <> "" and row < 53
    to both loops
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    08-21-2013
    Location
    Louisiana
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: How to Stop a Do While Loop at a certain row??

    Thanks, I was trying to put something at the bottom by Loop instead of the top. It worked fine!!

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: How to Stop a Do While Loop at a certain row??

    If you know the rows you are looping through why not use a For Next loop?
    If posting code please use code tags, see here.

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

    Re: How to Stop a Do While Loop at a certain row??

    If you use for...next loop as suggested by Norie, you can include empty cell checking inside the loop and exit the loop if empty cell is met

    ' no longer needed row = 8 'starting row for worksheet-1
                for row = 8 to 52
                if ActiveWorkbook.Sheets("WorkSheet-1").Cells(row, 1).Value = "" then exit for
                        With ThisWorkbook.Sheets("TAGSTEMPLATE")
    '... all your code
                        End With
                        PAGETAGNUM = PAGETAGNUM + 1
                        If PAGETAGNUM > 6 Then
                            Call printandclear
                            PAGETAGNUM = 1
                        End If
                Next row

+ 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. Loop Does Not Stop
    By ryanmorris in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-14-2011, 07:31 PM
  2. Can't get my loop to stop!!
    By berlinhammer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2009, 03:04 AM
  3. Loop until won't stop!?
    By meacho in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-27-2007, 03:37 PM
  4. Hot key to stop a LOOP
    By joopdog in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-09-2006, 02:51 PM
  5. Stop a loop
    By Matthew McManus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-11-2005, 04:55 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