CODEWITHSUNDEEP
vbaexcel
Developer
Developer

Reputation: 4331

Check string format

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

Upvotes: 2

Views: 859

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

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

Upvotes: 3

Related Questions