Macro to find odd due dates and move them

  • Thread starter jln via OfficeKB.com
  • Start date
J

jln via OfficeKB.com

All dates are in column E but the data that i get has
9/1/2006
9/10/2006 odd due date this would be moved to column F.
and the due date in column E would be changed to 10/1/2006.
So on odd due dates we forward the date to the first day of the next month.
What i would like to do i create a macro that goes thought Colunm E and moves
all dates that are not the fist of the month to column F in the same row and
change the date in column E to the first of the next month.
 
N

Nik

jln said:
All dates are in column E but the data that i get has
9/1/2006
9/10/2006 odd due date this would be moved to column F.
and the due date in column E would be changed to 10/1/2006.
So on odd due dates we forward the date to the first day of the next month.
What i would like to do i create a macro that goes thought Colunm E and moves
all dates that are not the fist of the month to column F in the same row and
change the date in column E to the first of the next month.
You can use this:

Sub Nik_test()
For z = 1 To Sheet1.UsedRange.Rows.Count
With Sheet1
If Day(.Cells(z, 5)) <> 1 Then
.Cells(z, 6) = .Cells(z, 5)
.Cells(z, 5) = DateSerial(Year(.Cells(z, 6)), _
Month(.Cells(z, 5)) + 1, 1)

End If
End With
Next

End Sub

If you have a header row on your column, change the z=1 to... to z=2 to...

Nik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top