CODEWITHSUNDEEP
excelvba
Sean Connecticut
Sean Connecticut

Reputation: 305

Data validity checks

I have a document that I want to check a number of columns to see if they are formatted correctly. For the values that are incorrect I want to have a msgbox that displays the values and locations (each on a separate line) of each instance.

Just in case there are a large number of mistakes I desire to do a messagebox for each column. Below I have a partial code but the cl.address2 / value2 need to change and possibly vbanextline as well but that's basically it. Thank you!

Sub datachecks
    Dim cl as range
    For each cl in range("A:A")
        If cl.format = "dd/mm/yyyy" then
            Msgbox(cl.address & cl.value & vbanextline & cl.address2 & cl.value)

Upvotes: 1

Views: 59

Answers (1)

K_B
K_B

Reputation: 3678

I would make the for each explicitly referring to the Cells. To keep track of the cells found use a local String variable, where you add any lines found and a carriage return or a line feed (= Chr(13)). Made some small corrections here and there as well, this should do it:

Sub datachecks()
    Dim cl As Range
    Dim foundCells As String

    For Each cl In Range("A:A").Cells
        If cl.NumberFormat = "dd/mm/yyyy" Then
            foundCells = foundCells & cl.Address & " " & cl.Value & Chr(13)
        End If
    Next cl
    MsgBox (foundCells)
End Sub

Upvotes: 1

Related Questions