If Then Looping Macro

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote