Glitch in code some where, i think ?

[Corey]

Have posted this eror before with no replies.

#############################################################
Sub callUpdates()
Sheets("Enter - Exit").Select ' Sheet name
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Application.Worksheets
ws.Select
With Range("E2") ' Date value displayed a 31 October 2006
..Value = Range("E2").Value + 7 ' Should add 7 days to currently displayed
date value
End With
Next
Sheets("Enter - Exit").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
############################################################


The above code suppose to add 7 days to a date value in Cell E2,
Yet sometimes it works fine,
yet other times it jumps 6 months backwards from 31 October 2006 - 11 July
2006.


Why ?

Is there an error in the code?

Corey....

 

[alondon]

Corey,

There is no error in the code, but the format of the E2 cell on each sheet
must be custom format "dd mmmm yyyy" to work consistently. Note, you were
wise to always use the European dating convention.

Allan P. London, CPA
(e-mail address removed)

 

[Corey]

How to i code that format ?

Corey,

There is no error in the code, but the format of the E2 cell on each sheet
must be custom format "dd mmmm yyyy" to work consistently. Note, you were
wise to always use the European dating convention.

Allan P. London, CPA
(e-mail address removed)

 

[JLGWhiz]

You might try it this way and see if it still does it. I basically put the
cell containing the date into a variable container and knocked out some
extraneous stuff. The key thing is using the variable container.

Sub callUpdates()
Sheets("Enter - Exit").Activate ' Sheet name
Dim ws As Worksheet, myDate As Date
myDate = Range("$E$2").Value
Application.ScreenUpdating = False
For Each ws In Application.Worksheets
ws.Activate
Range("E2") = myDate + 7
Next
Sheets("Enter - Exit").Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Tom Ogilvy]

If the cell actually contains a date serial number and not a string/text
which looks a date, then it should make no difference how it is formatted.

I suspect if you qualify your ranges (particularly if this is in a sheet
module rather than a general module) and use Value2, it will correct the
problem.

Sub callUpdates()
Sheets("Enter - Exit").Select ' Sheet name
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
With ws.Range("E2") ' Date value displayed a 31 October 2006
if isdate(.Value) then
.Value2 = .Value2 + 7
else
msgbox "Cell E2 on " & ws.name & " is not a date"
End if
End with
Next
Sheets("Enter - Exit").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Corey]

I still get the code jumping from 31 October 2006 to 11 July 2006 for some
reason????

But i have another macro that displays a userform that also has displayed
the value in Cell E2,
and it Shows 7 November 2006, whereas the worksheet E2 value shows 11 July
2006.

Why ?
Corey....

 

[Corey]

Tom,
Value did the same and jumped to 11 July 2006 when supposedly adding 7 days
to 31 October 2006???

Corey....

 

[Gary Keramidas]

are you sure you're not getting the values of E2 from 2 different sheets?

 

[Tom Ogilvy]

format the cell to General and report the value stored there.

 

[Tom Ogilvy]

I didn't say value.

--
Regards,
Tom Ogilvy

Tom,
Value did the same and jumped to 11 July 2006 when supposedly adding 7
days to 31 October 2006???

Corey....

 

[Corey]

39021

format the cell to General and report the value stored there.

 

[KLEBESTIFT]

I don't have a solution, but does the fact that we are looking for 07/11/06
and getting 11/07/06 look like a US/rest-of-the-world date conflict to anyone
else?