Solved: "Auto" update

[anthony20069]

Hi guys,

Need your excellent assistance again please

Ok so here is the dellema (think I spelt that wrong)

I have got a sheet and in now row G it has many rows of dates. Now each month I have to manualy go into this spreadsheet and update these dates )which are not all the same and also vary in months some of them) to show the new dates for the new month e.g.

From To
14/10/2008 --> 14/11/2008
2/09/2008 --> 2/10/2008

Now, i was wondering if some how there could be a way to update all these dates to show the next months date on the same day in VBA, i did try but all i could figure out was to do this:
[vba] range.("G2").value = range.("G2").value + 30 [/vba]
Which obv has many draw backs as i would need to do that for all 100 + dates as well as it doesnt give accurate dates.

Any help would be amazing


Thanks

Anthony

[holshy]

If you're already using a macro then this will work.[VBA] For i = 1 To 100 'assuming 100 lines of data
Set rng = Range("G1").Offset(i, 0)
rng = DateSerial(Year(rng), Month(rng) + 1, Day(rng))
Next i
[/VBA]

Without writing any VB, you could use the formula "=Date(year(G2),month(G2)+1,day(G2))" in another column, and then Copy and Paste Values.

[anthony20069]

Thanks for your fast reply, but i am a complete new person at this, so not really sure what you mean by

you could use the formula "=Date(year(G2),month(G2)+1,day(G2))" in another column, and then Copy and Paste Values.

I put that code you did into a new macro and assigned it to a button but nothing happenes when i click the button.

[Bob Phillips]

[vba]

For Each cell In Range(Range("G2"), Range("G2").End(xlDown)).Cells

cell.Value = Evaluate("=MIN(DATE(YEAR(" & cell.Address & "),MONTH(" & cell.Address & ")+{2,1},DAY(" & cell.Address & ")*{0,1}))")
Next cell
[/vba]

[rbrhodes]

Hi the code as supplied is not complete (by which I mean it needs to be inclosed in Sub and End Sub.

[VBA]
Sub ChangeDate()

...''some code...

End Sub

[/VBA]

You could also try this.

[vba]
Option Explicit
Sub ChangeDate()
Dim cel As Range
Dim rng As Range
Dim LastRow As Long
'Speed
Application.ScreenUpdating = False
'Get last row of data in Col G
LastRow = Range("G65536").End(xlUp).Row
'Create range objetc
Set rng = Range("G1:G" & LastRow)

'For Each is faster than loop
For Each cel In rng
'Check if December
If Month(cel) = 12 Then
'Yes, increment year
cel = DateSerial(Year(cel), Month(cel) + 1, Day(cel))
Else
'No, same year
cel = DateSerial(Year(cel) + 1, Month(cel) + 1, Day(cel))
End If
Next cel
'Destroy objects
Set cel = Nothing
Set rng = Nothing
'Reset
Application.ScreenUpdating = True
End Sub

[/vba]

[anthony20069]

Wonderfull worked like a charm... i to your might

Thanks all for your help