Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    If Then Looping Macro

    Hi!

    I have written the following code, which works, but then stops with an error at the end (so it really doesn't work ). Anyway, I think I shoud be using something other than the "For Each". I could use the "Do While" like I ususally do, however it's not a database and there are gaps in the information so a "Do While Range ("A" & x ) <> 0" stops the macro too soon and doesn't fill in the all the cells I'd like to fill in.

    I'd like to find the last row that has info in cell A, and then go up the worksheet from there looking for the cells that say "Plant" in column A, and then put the words "13 Month Average" in column O. Any help is always appreciated!

    Thanks!!
    Lana


    Sub Macro13()
    Dim x As Long

    x = 50

    For Each Row In Rows
    If Range("A" & x) = "Plant" Then
    Range("O" & x) = "13 Month Average"
    Selection.Font.Bold = True

    Else: Range("O" & x) = ""
    End If
    x = x - 1

    Next

    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    This uses a "Do while...":

    Code:
    Sub Macro13()
      Dim x As Long
      x = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
      Do While x > 0
        If Range("A" & x) = "Plant" Then
          Range("O" & x) = "13 Month Average"
          Range("A" & x & ":O" & x).Font.Bold = True
        Else: Range("O" & x) = ""
        End If
        x = x - 1
      Loop
    End Sub
    But a For.. Next could also be done.
    Steve
    Last edited by sdckapr; 2011-10-10 at 14:14.

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    LJM (2011-10-10)

  4. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,176
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You will notice Steve has avoided using "select" and names the range explicitly. This prevents Excel doing more work than is required and speeds up the macro.

    cheers, Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •