PDA

View Full Version : Solved: "Auto" update



anthony20069
10-14-2008, 02:12 PM
Hi guys,

Need your excellent assistance again please : pray2:: pray2:

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:
range.("G2").value = range.("G2").value + 30
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
:bow:

Thanks

Anthony

holshy
10-14-2008, 02:32 PM
If you're already using a macro then this will work. 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


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
10-14-2008, 02:51 PM
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
10-14-2008, 02:56 PM
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

rbrhodes
10-14-2008, 07:22 PM
Hi the code as supplied is not complete (by which I mean it needs to be inclosed in Sub and End Sub.


Sub ChangeDate()

...''some code...

End Sub



You could also try this.


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

anthony20069
10-15-2008, 02:07 AM
:beerchug:

Wonderfull worked like a charm... i :bow: to your might :)

Thanks all for your help