+ Reply to Thread
Results 1 to 3 of 3

Do... Loop macro problem

Hybrid View

  1. #1
    Registered User
    Join Date
    12-08-2008
    Location
    Hampton, VA
    Posts
    8

    Do... Loop macro problem

    Sorry if this code isn’t written elegantly – I am new to VBA and am learning as I go!

    I’m working on a macro to clear the contents of all the cells in a certain area of the worksheet that are not locked and do not contain a hyperlink. (C7 is the first data-entry cell; EndPage1 is a specific cell near the bottom right of the sheet that sets the outside range of the cells to be cleared. This is the code so far:

    Dim Message, Style, Title, Response As String
    Dim CMax, RMax, CCounter, RCounter As Integer
    '
    Message = "Are you sure you want to clear Page 1?" & Chr(10) & "All data entered on this tab will be lost."
    Style = vbOKCancel + vbQuestion + vbDefaultButton1
    Title = "Warning"
    '
    Response = MsgBox(Message, Style, Title)
    If Response = vbCancel Then
        Application.Goto reference:=Range("$c$7")
        '
        Else
        Worksheets("page 1").Unprotect Password:=""
        Application.Goto reference:="EndPage1"
        RMax = ActiveCell.Row
        CMax = ActiveCell.Column
        CCounter = 1
        RCounter = 1
        '
        Do
            Application.Goto reference:=Range("a" & RCounter)
            Do
                If ActiveCell.Locked = False Then
                    If ActiveCell.Hyperlinks.Count = 0 Then
                        ActiveCell.MergeArea.ClearContents
                        End If
                    End If
                ActiveCell.Offset(0, 1).Activate
                CCounter = ActiveCell.Column
                Loop Until CCounter = CMax
            RCounter = RCounter + 1
            Loop Until RCounter = RMax
        Application.Goto reference:=Range("$c$7")
        Worksheets("page 1").Protect Password:=" "
        
        End If
        
    End Sub
    It clears about the first 65 rows beautifully, then on the next row it gets “stuck” in the innermost do-loop and continues across the page rather than looping back to start in column A. Eventually it gets hung up and gives me an error. I can’t figure out why. Can anyone see where I’m going wrong?

    Thanks!

    ETA - saw the problem - see below.
    Last edited by RSimmons; 12-08-2008 at 06:02 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    Welcome to the forum.

    Unmerging all cells would be a good place to start.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-08-2008
    Location
    Hampton, VA
    Posts
    8
    Thanks!

    Unfortunately I inherited this form, it's huge, and unmerging all the cells would be an undertaking on its own.

    Merging was at the root of the problem, though. (I feel dumb now - I posted and 10 seconds later saw the problem. This after 2 hours of heartburn.) I had to change it to "Loop Until CCounter > CMax" ( > rather than = ). Doh.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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