Function Call

[Darren Smith]

Many thanks in advance

Using a function to check a column "E" to the last row if the date format is "DD/MM/YYYY" then it needs to copy the data to the next column "F"
Says DateFormatCheck "Argument not optional" when I try to add function to the command button

Command button code below

Private Sub cmdUpdate_Click()

Dim res As Integer
Dim ws As Worksheet


  Set ws = ThisWorkbook.Worksheets("TGS JOB RECORD")

With ws

  If DateFormatCheck = True Then .Range("E2:E").Copy Range("F2:F")
    End If
    End With
    
ThisWorkbook.RefreshAll

      End Sub

Then Function Code below

Function DateFormatCheck(cells As Range) As Boolean

Dim ws As Worksheet
Dim Rng As Range
Dim LRow As Long

Set ws = thisworkbookworksheets("TGS JOB RECORD")

Set LRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set Rng = ws.Range("E2:E" & LRow)

    DateFormatCheck = False
    If IsDate(Rng.Cells.Value) Then
        If Rng.Cells.NumberFormat = "dd/mm/yyyy;@" Then
            DateFormatCheck = True
        ElseIf CInt(Mid(Rng.Cells.Value, 1, 2)) <= 12 Then
            DateFormatCheck = True
        End If
    End If
End Function

 

[diddi]

If DateFormatCheck (   insert the range you are checking here   ) = True Then .Range("E2:E").Copy Range("F2:F")

 

[Darren Smith]

This is still an argument not optional

If DateFormatCheck = ws.Range("E2:E" & LastRow) = True Then
ws.Range("E2:E").Copy Range("F2:F")

Just to be clear column E has other values which are not dates so it needs to miss them out?

 

[Darren Smith]

Lastest Function below

Function DateFormatCheck(cell As Range) As Boolean

DateFormatCheck = False
    If IsDate(cell.Value) Then
        If cell.NumberFormat = "dd/mm/yyyy;@" Then
            DateFormatCheck = True
        ElseIf CInt(Mid(cell.Value, 1, 2)) <= 12 Then
            DateFormatCheck = True
        End If
    End If
End Function

 

[RoryA]

That's not how you call it - it's:

If DateFormatCheck(ws.Range("E2:E" & LastRow)) = True Then

 

[Darren Smith]

Thanks, Rory that`s done the trick but it will not copy & Paste the detail?
Not all of the cells in that column E are dates will that mess up the function??

 

[RoryA]

Your function isn't written to handle multiple cells, and it is not clear what you want to happen if some of the cells are not in date format? (also, just to be clear, just because a cell's numberformat matches that date format it does not in any way mean that the cell actually contains a date)

 

[Darren Smith]

The idea is to find all cells in column E with the date then copy the dates E into column F. Keeping the same format & row as the original date cells in column E.
So how would you go about finding dates in a column?

 

[RoryA]

That doesn't really answer the question I'm afraid. Do you just want to copy cells with dates and ignore all others? If so, should the copied dates go to the same row in column F or to the next available row? Your current code looks like it is intended to only copy any data if all the cells are dates, otherwise it won't do anything at all. You can use IsDate to test if the contents of a cell look like a date - though it could be stored as text, so you'd need to decide what you want to do in that case.

 

[Darren Smith]

Do you just want to copy cells with dates and ignore all others? Yes
If so, should the copied dates go to the same row in column F or to the next available row? On the same row
I think IsDate sounds like a good idea