A VBA Question related to DATE

[wenyuanalive]

Hi Guys,

There are two columns in a spreadsheet, Column A is Document Date, Column B is Document Period.
There are already dates in Column A. (There could be as many as 2000 dates in Column A)


How can I write a VBA code so that the output in Column B would be:


The rules are:
1. Add one more month in Column B. For example, Cells(A2) = 5/9/2011, add one month to May( means June), Cells(B1) = 06/2011. Note that there is an additional '0' before 6, and there is no day value(only month and year in this case).

2. If the year in Column A is 2012, don't put year value in Column B. If the year value in Column is not 2012, put the year value in Column B. For example, Cells(A2) = 5/9/2011, it's Year 2011, not 2012. So put year 2011 in Column B, and Cells(B1) = 06/2011. However, Cells(A3) = 4/18/2012, it's Year 2012. So just put '05' in the next column without '/2012'.

3. If it's blank in Column A, leave it blank in Column B.

Any suggestion is welcomed!

[CodeNinja]

Going to lunch soon, so I didn't really test this, but I think something like the following would do what you want...


[VBA]Sub test()
Dim dt As Date
Dim i As Integer
For i = 2 To Sheet1.Range("A65536").End(xlUp).Row
If IsDate(Sheet1.Cells(i, 1)) Then
dt = Sheet1.Cells(i, 1)
dt = DateSerial(Year(dt), Month(dt) + 1, 1)
If Year(dt) = 2012 Then
Sheet1.Cells(i, 2) = Month(dt)
Else
Sheet1.Cells(i, 2) = Month(dt) & "/" & Year(dt)
End If
End If
Next i

End Sub[/VBA]

[wenyuanalive]

Going to lunch soon, so I didn't really test this, but I think something like the following would do what you want...


[VBA]Sub test()
Dim dt As Date
Dim i As Integer
For i = 2 To Sheet1.Range("A65536").End(xlUp).Row
If IsDate(Sheet1.Cells(i, 1)) Then
dt = Sheet1.Cells(i, 1)
dt = DateSerial(Year(dt), Month(dt) + 1, 1)
If Year(dt) = 2012 Then
Sheet1.Cells(i, 2) = Month(dt)
Else
Sheet1.Cells(i, 2) = Month(dt) & "/" & Year(dt)
End If
End If
Next i

End Sub[/VBA]

Thanks for your prompt reply!
Here's another awesome code someone gave me:
[VBA]Sub FillPeriod()
Dim r As Long
Dim m As Long
Dim v As Variant
m = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To m
v = Range("A" & r).Value
If v = "" Then
Range("B" & r) = ""
Else
v = DateSerial(Year(v), Month(v) + 1, 1)
If Year(v) = Year(Date) Then
Range("B" & r) = "'" & Format(v, "mm")
Else
Range("B" & r) = "'" & Format(v, "mm\/yyyy")
End If
End If
Next r
End Sub[/VBA]

[Kenneth Hobs]

Please do not quote unless you really need to and usually just a small part. Please add VBA code tags when posting code.

Right click the sheet's tab, View Code, and paste this. Cut and paste your column A data to update Column B data and number format in column B. After that, any change in Column B will update as needed.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range, nFormat As String
Set r = Intersect(Range("A2", Range("A" & Rows.Count).End(xlUp)), Target)
If r Is Nothing Then Exit Sub

Application.EnableEvents = False

For Each c In r
With c
If IsEmpty(c) Or .Value = "" Then
.Offset(0, 1).Value = ""
Else
.Offset(0, 1).Value = DateAdd("m", 1, c.Value)
If Year(.Value) = 2012 Then
'If Year(.Value) = Year(Date) Then
nFormat = "mm"
Else
nFormat = "mm/yyyy"
End If
.Offset(0, 1).NumberFormat = nFormat
End If
NextC:
End With
Next c

Application.EnableEvents = False
End Sub[/VBA]

[wenyuanalive]

Thanks for the vba format. Didn't know that before.

Just curious why don't you let me quote? I think that looks much clearer about whose post I replied to. Is it a rule or sth?

[Aussiebear]

Its more about the readability of the post.

[Kenneth Hobs]

The FAQ explains the VBA codes tags which is one of the great things about this forum. Most others use code code tags which is not as cool.

Since I am not a moderator here, I make no rules. I just give advise.

There are not many forums that tell you about excessive quoting but too much of a good thing is not good. Just say something like, the code in Post #4 works for me. This goes back to the old days of email and usenet newsgroups. Quoting is needed even less now with the advent of threaded forums like this one. It is a tool, use as needed for effect.

http://dictionary.reference.com/browse/netiquette?s=t