hermithead
New Member
- Joined
- Sep 7, 2009
- Messages
- 37
OK Im one step away from completing my spreadsheet and I have this problem which has been ongoing throughout development.
The dates that I am pasting into my spreadsheet are formatted as dd.mm.yyyy , the macro that I run to check that the dates are correct will only work if the date is in dd/mm/yyyy format. So I do a simple Find and Replace ie "." for "/".
The problem is after the Find and Replace some not all of the dates are rearranged as: mm/dd/yyyy. This of course throws out my data validation.
Note, I've tried Format cells>Date but the macro still doesnt work and only recongnises "/". (Ive included the macro below in case theres a workaround there instead).
<!-- BEGIN TEMPLATE: bbcode_code -->
<!-- / message --> <!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --> <!-- END TEMPLATE: ad_showthread_firstpost_sig --> <!-- controls -->
The dates that I am pasting into my spreadsheet are formatted as dd.mm.yyyy , the macro that I run to check that the dates are correct will only work if the date is in dd/mm/yyyy format. So I do a simple Find and Replace ie "." for "/".
The problem is after the Find and Replace some not all of the dates are rearranged as: mm/dd/yyyy. This of course throws out my data validation.
Note, I've tried Format cells>Date but the macro still doesnt work and only recongnises "/". (Ive included the macro below in case theres a workaround there instead).
<!-- BEGIN TEMPLATE: bbcode_code -->
Code:
<style> .alt2 font { font: 11px monospace !important; color: #333 !important; } </style>
[FONT=Arial][SIZE=2][COLOR=#000000]'Checks date range'
Sheets("Calc").Select
Range("A9").Select
Dim iMonth As Integer
A:
iMonth = Month(ActiveCell.Value)
If iMonth = 7 Then
ActiveCell.Offset(1, 0).Select
GoTo A
Else
If ActiveCell.Value = "" Then
MsgBox "All dates are within range", vbInformation
Else
MsgBox "This is not July data", vbCritical
Exit Sub
End If
End If[/COLOR][/SIZE][/FONT]
<!-- END TEMPLATE: bbcode_code -->
