Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looping | Excel Programming | |||
Help with Looping I think | Excel Programming | |||
Looping | Excel Programming | |||
Looping | Excel Programming | |||
looping to End | Excel Programming |