Looping
 

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.

Looping
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BoRed79" wrote in message
...
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.

Looping
 

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
--
Cheers,
Ryan


"BoRed79" wrote:

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.

Looping
 

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


"Ryan H" wrote:

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
--
Cheers,
Ryan


"BoRed79" wrote:

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.