Check string format

Question

I have a loop, that loops all entries in a column:

Dim indexOfDATE As Integer
indexOfDATE = 3
Do Until indexOfDATE - 1 = Cells(Rows.Count, 2).End(xlUp).Row
    Dim tempDate As String
    tempDate = Replace(Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value, ",", ".")
    tempDate = Replace(tempDate, ".", "/")
    indexOfDATE = indexOfDATE + 1
Loop

I need to check that my tempDate string variable is in a dd.MM.yyyy format how can i do this and if not, show message box, not fall down with an error?

EDIT:

I'm doing this:

Dim indexOfDATE As Integer
indexOfDATE = 3
Do Until indexOfDATE - 1 = Cells(Rows.Count, 2).End(xlUp).Row
    Dim tempDate As String
    tempDate = Replace(Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value, ",", ".")
    tempDate = Replace(tempDate, ".", "/")
    Dim current As Date
    current = Format(CDate(tempDate), "dd/mm/yyyy")
    Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value = current
    indexOfDATE = indexOfDATE + 1
Loop

As you see the cell is a string cell, i need to ensure that tempDate string will be in a correct format before i do convertation or application will fall.

I want to show user message, that in Cell (,) he has incorrect data

Or maybe a Try Catch block on convertation - but i have failed to stop code execution after first error

@Cheese, you need to check whether tempDate is in correct format (dd.MM.yyyy or dd/MM/yyyy) or that tempDate is correct date? — Dmitry Pavliv
– Dmitry Pavliv, Commented Jan 30, 2014 at 9:58

Siddharth Rout · Accepted Answer · 2014-01-30 09:56:42Z

Try this (UNTESTED)

Dim indexOfDATE As Long
Dim tempDate As String
Dim current As Date

indexOfDATE = 3

Do Until (indexOfDATE - 1) = Cells(Rows.Count, 2).End(xlUp).Row
    tempDate = Replace(Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value, ",", "/")

    If IsDate(tempDate) Then
        current = Format(CDate(tempDate), "dd/mm/yyyy")
        With Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2)
            .NumberFormat = "dd/mm/yyyy"
            .Value = current
        End With
        indexOfDATE = indexOfDATE + 1
    End If
Loop

Or more shorter version

Dim indexOfDATE As Long
Dim tempDate As String
Dim current As Date

indexOfDATE = 3

With Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2)
    Do Until (indexOfDATE - 1) = Cells(Rows.Count, 2).End(xlUp).Row
        tempDate = Replace(.Value, ",", "/")

        If IsDate(tempDate) Then
            current = Format(CDate(tempDate), "dd/mm/yyyy")
            .NumberFormat = "dd/mm/yyyy"
            .Value = current
            indexOfDATE = indexOfDATE + 1
        End If
    Loop
End With

Collectives™ on Stack Overflow

Check string format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related