Looping

[BoRed79]

I have some VBA code which adds a month into cell Y3 if there is any value in
cell A3 and no value already in cell Y3 (see below):

Sub AddMonth()

If (Range("A3").Value <> "" And Range("Y3").Value = "") Then
Range("Y3").Select
ActiveCell.FormulaR1C1 = "Dec-2009"
Selection.NumberFormat = "mmm-yy"
End If

End Sub

I would like to set this up to loop so that it looks at all of the cells in
column A and Y for values and adds a date where appropriate.

Can anyone advise how I can loop this code?

Thanks.

Liz.

 

[Don Guillett]

Sub addmonthloop()
For Each c In Range("a2:a22")
If Len(Application.Trim(c)) > 0 And _
Len(Application.Trim(c.Offset(, 24))) = 0 Then
c.Offset(, 24) = "Dec-2009"
End If
Next c
End Sub

 

[Ryan H]

Try this code. This will find the last cell with a value in it in Col A. I
assumed you wanted to start at row 3, thus the loop will run from row 3 to
the last row.

Note: There is no need to select cells in VBA. So I took the liberty of
shortening your code to make if more efficient. Are you always wanting to
put Dec-2009 into Col Y? Or are you wanting to put the month the macro was
ran?

Hope this helps! If so, let me know, click "YES" below.

Sub AddMonths()

Dim LastRow As Long
Dim c As Range

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For Each c In Range("A3:A" & LastRow)
If c.Value <> "" And Cells(c.Row, "Y").Value = "" Then
Cells(c.Row, "Y").Value = Format("Dec-2009", "mmm-yy")
End If
Next c

End Sub

 

[BoRed79]

Thanks for your help - I made one small modification (to replicate it for a
number of sheets) and it works great - many thanks!