date validation should not be > today()

[ramserp]

Hi Friends,

I have string in sheet source which has date part, that date part should not be >today(). When I extract date from source sheet to destination sheet, if the date part has the date >today() it should not load into destination sheet.

Source sheet data

<TABLE style="WIDTH: 80pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=107 border=0 x:str><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=107 height=17>Exp. No</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>071620009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>15252009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>09252009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>13162009_n2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>07162009_n3</TD></TR></TBODY></TABLE>

I copied my code here for your reference. Please give me the appropriate solution how to put conditon for this.

Sub exp()
Dim v_counter As Integer
Dim v_expnum As String
Dim v_lastrow As Integer
Dim v_date As Variant
Dim v_success As String
v_counter = 2
v_success = "YES"
v_lastrow = Sheets("Source").Range("A65536").End(xlUp).Row
For v_counter = 2 To v_lastrow
v_expnum = Sheets("source").Range("A" & v_counter).Value

v_date = Split(v_expnum, "_")
If IsEmpty(v_expnum) Then
v_success = "NO"
ElseIf Not IsEmpty(v_expnum) And v_success = "YES" Then
If Len(v_date(0)) = 8 Then
' assigning day,month and year to the 3 different variables
v_year = Mid(v_expnum, 5, 4)
v_month = Left(v_expnum, 2)
v_day = Mid(v_expnum, 3, 2)

If v_month < 1 Or v_month > 12 Then
v_success = "NO"

ElseIf v_month >= 1 Or v_month <= 12 Then
Sheets("Destination").Range("A" & v_counter).Value = DateSerial(v_year, v_month, v_day)

End If
Else
v_success = "NO"
End If
End If
Next v_counter
End Sub


Thanks & Regards
Ramesh

 

[somnath_it2006]

Error sheet means "Destination" sheet????

 

[ramserp]

Hi Somnath,

I have given wrong sheet name to you. If it is wrong date it is not move to destination. It should be in the same sheet.


Thanks
Ramesh

 

[somnath_it2006]

Hello Ramesh,

Could you please conform below statement:

ElseIf Not IsEmpty(v_expnum) And v_success = "YES" Then

Why you have checked v_success = "YES".
Because according to this, if loop found any wrong date at any state it will not process next dates.

 

[somnath_it2006]

If that statement is OK then... below is the code in which your both conditions checked.

Option Explicit

Sub exp()
    Dim v_counter As Integer
    Dim v_expnum As String
    Dim v_lastrow As Integer
    Dim v_date As Variant
    Dim v_success As String
    Dim v_SampleDate As String
    Dim v_year, v_month, v_day As String
    
    
    v_counter = 2
    v_success = "YES"
    v_lastrow = Sheets("Source").Range("A65536").End(xlUp).Row
    
    For v_counter = 2 To v_lastrow
        v_expnum = Sheets("source").Range("A" & v_counter).Value
        
        v_date = Split(v_expnum, "_")
        
        If IsEmpty(v_expnum) Or UBound(v_date) < 1 Then
            v_success = "NO"
        
        ElseIf Not IsEmpty(v_expnum) And v_success = "YES" Then
        
            If Len(v_date(0)) = 8 Then
                ' assigning day,month and year to the 3 different variables
                v_year = Mid(v_expnum, 5, 4)
                v_month = Left(v_expnum, 2)
                v_day = Mid(v_expnum, 3, 2)
                
                v_SampleDate = v_day & "/" & v_month & "/" & v_year
                
                If IsDate(v_SampleDate) = False Then
                    v_success = "NO"
                    
                ElseIf DateValue(v_SampleDate) < Date Then
                    Sheets("Destination").Range("A" & v_counter).Value = DateSerial(v_year, v_month, v_day)
                    
                Else
                    v_success = "NO"
                    
                End If
                
            Else
                v_success = "NO"
        
            End If
        
        End If
        
    Next v_counter
    
End Sub

 

[ramserp]

Hello Somnath,

I declared v_success variable for flag. If the condition is true then v_success="NO" else v_success="YES".

When v_success="YES" the move to the destination sheet but I didn't used v_success="YES" method to store values.


Thanks
Ramesh

 

[somnath_it2006]

Ok.. So without flag you can apply proper logic.

Check the below code with comments:

Option Explicit

Sub exp()
    Dim v_counter As Integer
    Dim v_expnum As String
    Dim v_lastrow As Integer
    Dim v_date As Variant
    Dim v_success As String
    Dim v_SampleDate As String
    Dim v_year, v_month, v_day As String
    Dim DestStatus As Long
    
    
    
    
    ' Get last row number
    v_lastrow = Sheets("Source").Range("A65536").End(xlUp).Row
    
    ' Counter for destination sheet data status
    DestStatus = 2
    
    ' Loop from 2 to last data row of sheet
    For v_counter = 2 To v_lastrow
        
        ' Get date from cell
        v_expnum = Sheets("source").Range("A" & v_counter).Value
        
        ' Split date by "_"
        v_date = Split(v_expnum, "_")
        
        ' If date cell is empty or after split there is no value found in
        ' Second index of array (means there is no "_" found) then goto next value
        If IsEmpty(v_expnum) Or UBound(v_date) < 1 Then
            GoTo NextV_Counter
        
        Else
            
            ' If first part of index is like date (8 digit) then
            If Len(v_date(0)) = 8 Then
                
                ' assigning day,month and year to the 3 different variables
                v_year = Mid(v_expnum, 5, 4)
                v_month = Left(v_expnum, 2)
                v_day = Mid(v_expnum, 3, 2)
                   
                ' Prepare propar date string
                v_SampleDate = v_day & "/" & v_month & "/" & v_year
                
                ' Check string is date or not
                If IsDate(v_SampleDate) = False Then
                    GoTo NextV_Counter
                
                ' If date is less than todays date then only move to destination sheet
                ElseIf DateValue(v_SampleDate) < Date Then
                    Sheets("Destination").Range("A" & DestStatus).Value = DateSerial(v_year, v_month, v_day)

                    ' Increase the row counter
                    DestStatus = DestStatus + 1
                    
                Else
                    GoTo NextV_Counter
                End If
                
            Else
                GoTo NextV_Counter
        
            End If
        
        End If
        
' Jump to next cell counter
NextV_Counter:
        

    Next v_counter
    
End Sub

Revert in case of other queries.

 

[ramserp]

Hi Somnath,

The logic implemented by you is good.

Thank you very much for spending your valuable time for giving reply to my posts.


Thanks & Regards
Ramesh