Function Call

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
631
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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

VBA Code:
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
VBA Code:
If DateFormatCheck (   insert the range you are checking here   ) = True Then .Range("E2:E").Copy Range("F2:F")
 
Upvote 0
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?
 
Upvote 0
Lastest Function below

VBA Code:
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
 
Upvote 0
That's not how you call it - it's:

Code:
If DateFormatCheck(ws.Range("E2:E" & LastRow)) = True Then
 
Upvote 0
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??
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top