Need Macro to Check Format of Cells In A Column

[dcofer]

Hell All,

I need a macro, preferably, but a formula might work to check the contents of the cells in Column C of my report, which is usually about a thousand rows long.

The format should be like this example: 08-12-2017 DCV It needs a two digit month, a dash, a two digit day of the month, a dash, and a four digit year, followed by one space and the letters DCV in all caps.

The macro can simply check the third and sixth positions for a dash, the 11th position for a space and the twelfth, thirteenth and fourteenth position for the letters "DCV" in all caps.

Thanks in advance for your assistance.

David

 

[Fluff]

Try this
It will put either OK or Nope into Column D

Sub Ccheck()
' dcofer
    Dim Cnt As Long
    
    For Cnt = 2 To Range("C" & Rows.Count).End(xlUp).Row
        If Range("C" & Cnt).Value Like "##-##-#### DCV" Then
            Range("D" & Cnt) = "ok"
        Else
            Range("D" & Cnt) = "Nope"
        End If
    Next Cnt

End Sub

 

[dcofer]

Fluff,

Thanks for your help. But this put NOPE on every entry and most are correct. I reviewed the macro, and it looks like it would work.

Any idea why it put NOPE for every entry?

 

[Fluff]

Are the contents of col C actual values, or are they the result of a formula?

 

[dcofer]

Are the contents of col C actual values, or are they the result of a formula?

They are actual values.

I was able to write a formula and then using the macro recorder, I recorded a macro which works.

My macro is below:

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 6.44
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(MID(RC[-1],3,1)=""-"",MID(RC[-1],6,1)=""-"",MID(RC[-1],11,1)="" "",MID(RC[-1],12,3)=""DCV""),""YES"",""NO"")"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D335")
Range("D1:D335").Select
Range("D1:D335").Copy
Range("D1:D335").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub


Thanks for your time and assistance.

 

[Fluff]

Glad you got it sorted

 

[dcofer]

Thanks, it is probably not the cleanest code, but it worked.