Converting String to Date with DateValue, then testing for T/F

[JoshExcel]

hello again.

I am trying to check my sheet names to see if they convert to a valid date format. If it doesn't, then I want it to check for the next criteria (ex - if it equals "Page2"). With the code below I keep getting an error of type mismatch. Any help would be greatly appreciated. Code is below.

    Dim dateTester As String

    wbChoice.Activate
    
    For i = 1 To Sheets.Count
        dateTester = Left(wbChoice.Sheets(i).Name, 2) & "/" & Mid(wbChoice.Sheets(i).Name, 3, 2) & "/" & Right(wbChoice.Sheets(i).Name, 2)
        'MsgBox (dateTester)
        If IsError(DateValue(dateTester)) = True Then
             If wbChoice.Sheets(i).Name <> "Page2" Then

[protonLeah]

Sub x()
Dim y As Date
dateTester = "06/21/96"
On Error Resume Next
y = DateValue(dateTester)
If Err.Number = 0 Then
    MsgBox "valid date"
Else
    Err.Clear
    If wbChoice.Sheets(i).Name <> "Page2" Then
        'etc
    End If
End If
End Sub

[Izandol]

You may also use:

If Not IsDate(dateTester) Then

[JoshExcel]

ok, maybe I'm just a little thick, but I don't see how that will loop through all the worksheets in the workbook. I may have left that part off in my original post though.

Here is my revised code to try to use what proton suggested.

I still receive the error message of 13 "Type Mismatch" on the "dateTester = DateValue(dateText)". I even tried to call dateTester as a Boolean, then checking for False, but I still get the Mismatch message. All suggestions are welcomed.

    Dim dateText As String
    Dim dateTester As Date
    wbChoice.Activate
    
    For i = 1 To Sheets.Count
        dateText = Left(wbChoice.Sheets(i).Name, 2) & "/" & Mid(wbChoice.Sheets(i).Name, 3, 2) & "/" & Right(wbChoice.Sheets(i).Name, 2)
        MsgBox (dateText)
        dateTester = DateValue(dateText)
        If Err.Number <> 0 Then
            If wbChoice.Sheets(i).Name <> "Effic" Then
                If wbChoice.Sheets(i).Name <> "Veps Util" Then
                    If wbChoice.Sheets(i).Name <> "OU" Then
                        If wbChoice.Sheets(i).Name <> "Prod" Then
                            If wbChoice.Sheets(i).Name <> "Quality" Then
                                'wbChoice.Sheets(i).Columns("E:E").Select
                                'Selection.Copy
                                'Columns("F:F").Select
                                'Selection.Insert Shift:=xlToRight
                                'Range("F1") = "% VPH to Goal"
                                MsgBox (wbChoice.Sheets(i).Name)
                            End If
                        End If
                    End If
                End If
            End If
        End If
    Next i

[JoshExcel]

I even tried to incorporate Izandol's suggestion, but keep getting the type mismatch error.

[Izandol]

Where does error occur using IsDate function?

[JoshExcel]

****well, it usually works better if I remove the ' in order for it to execute that line of code. Updated code soon to follow.

whether I use Not IsDate(dateText), or what I have below, it doesn't loop the way I want it to. Basically, I want it to update all the sheets, unless the text can convert to a date, or if the text = "Effic", "Veps Util", "OU", "Prod" or "Quality". All the other sheets get an extra column added.

        Dim dateText As String
        Dim dateTester As Date

        For i = 1 To Sheets.Count
        dateText = Left(wbChoice.Sheets(i).Name, 2) & "/" & Mid(wbChoice.Sheets(i).Name, 3, 2) & "/" & Right(wbChoice.Sheets(i).Name, 2)
        MsgBox (dateText)
        'dateTester = DateValue(dateText)
        If IsDate(dateText) = True Then
            If wbChoice.Sheets(i).Name <> "Effic" Then
                If wbChoice.Sheets(i).Name <> "Veps Util" Then
                    If wbChoice.Sheets(i).Name <> "OU" Then
                        If wbChoice.Sheets(i).Name <> "Prod" Then
                            If wbChoice.Sheets(i).Name <> "Quality" Then
                                'wbChoice.Sheets(i).Columns("E:E").Select
                                'Selection.Copy
                                'Columns("F:F").Select
                                'Selection.Insert Shift:=xlToRight
                                'Range("F1") = "% VPH to Goal"
                                MsgBox (wbChoice.Sheets(i).Name)
                            End If
                        End If
                    End If
                End If
            End If
        End If
    Next i

[JoshExcel]

Here is the functioning code. Thanks Izandol!

    For i = 1 To Sheets.Count
        dateText = Left(wbChoice.Sheets(i).Name, 2) & "/" & Mid(wbChoice.Sheets(i).Name, 3, 2) & "/" & Right(wbChoice.Sheets(i).Name, 2)
        'MsgBox (dateText)
        If Not IsDate(dateText) Then
            If wbChoice.Sheets(i).Name <> "Effic" Then
                If wbChoice.Sheets(i).Name <> "Veps Util" Then
                    If wbChoice.Sheets(i).Name <> "OU" Then
                        If wbChoice.Sheets(i).Name <> "Prod" Then
                            If wbChoice.Sheets(i).Name <> "Summary" Then
                                If wbChoice.Sheets(i).Name <> "Quality" Then
                                    wbChoice.Sheets(i).Select
                                    Columns("C:C").Select
                                    Selection.Copy
                                    Columns("F:F").Select
                                    Selection.Insert Shift:=xlToRight
                                    Range("F2") = "% VPH to Goal"
                                    Range("F3").Formula = "=VLOOKUP($A3,INDIRECT(""Effic!$""&SUBSTITUTE(ADDRESS(1,MATCH(""% VPH to Goal"",Effic!$1:$1,0),4),""1"","""")&"":$""&SUBSTITUTE(ADDRESS(1,(MATCH(""Total"",Effic!$2:$2,0)+MATCH(""% VPH to Goal"",Effic!$1:$1,0)),4),""1"","""")),MATCH($A$1,Effic!$2:$2,0),)"
                                    Range("F3").Copy
                                    lRow = wbChoice.Sheets(i).Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row
                                    'MsgBox (lRow)
                                    For x = 4 To lRow
                                        Range("F" & x).Select
                                        ActiveSheet.Paste
                                    Next x
                                    Application.CutCopyMode = False
                                    'MsgBox (wbChoice.Sheets(i).Name)
                                End If
                             End If
                        End If
                    End If
                End If
            End If
        End If
    Next i