vb manipulation of date data on sheet

[asylum]

Hi, I have a sheet with a string of date, 365 lines long as below..

03/01/2016 Sunday 1
04/01/2016 Monday 2
05/01/2016 Tuesday 3
.
.
.

I am trying to write a bit of code to manipulate the sheet so that it inserts a line at the start of every month, and in column A of that row it puts the Name (in text) of the month following, so i end up with:

January
03/01/2016 Sunday 1
.
.

etc

so far i have:

Sub monthdef()

Dim month As Variant
Dim R As Range

month = Range("a2").Value

MsgBox month

For Each R In Range("c2:c367")

If R.Value = 1 Then

rows(R.Row).Select
Selection.Insert shift = xlDown
Range("a" & R.Row - 1).Select
ActiveCell.Value = month

Range("c" & R.Row + 15).Select

month = month + 32

MsgBox month

End If

Next R

End Sub

but this cant 'jump' the first 1 it comes to! and I don't know the code to get the text value of month into the row eiether.

Can you help?

Thanks

Andy

 

[gsistek]

Is your list of dates every day in the month or just the first of the months? Also, you have January appear right before 3/1/2016. How do you determine what month to display?

 

[asylum]

hi, yes it is a list of every day in the month, but it may be a different month at the start of the list every time.

the dtae shown above is an unfortunate typo, they should all start with 01/01, howevr the determination of when a month ends and the line being inserted is done via the 1,2,3 etc in column c, which is the day of the month, when this is a 1 i insert a line above.

Thanks

 

[Warship]

Try

Sub monthdef()
  Dim R
  For R = 367 To 2 Step -1
    If Cells(R, 3) = 1 Then
      Cells(R, 3).EntireRow.Insert shift:=xlDown
      Cells(R, 1) = MonthName(Month(Cells(R + 1, 1)))
    End If
  Next R
End Sub

 

[asylum]

Hi Warship, thats great thanks, I had just come to the realisation that I would have to work UP the list and not down, great solution, many thanks for your help!

Andy