ive been trying to learn vba in the past 2 weeks, but now im kinda stuck due to an issue. Dealing with dates been quite hard. But lets get to the point, ive been trying to copy data from a table(Dados) which is within a sheet to another sheet(sheet2).But before doing so the user have to properly inform wats the date interval(which are date1 and date2) he wishes to filter and it has to go through some validations like:

• The fields shouldnt be empty
• The date format must be valid
• The initial date must not be greater than the final date
• The initial date must not be bigger than todays date

Than it filters the table, and copy and paste the table for the other sheet. Heres the table: Thne image may not be so clear, but basically i need to filter the table based on that date range and sent it to the other sheet

Hopefully i can get some help, heres my current code which i managed to get so far:

Option Explicit
Sub DateCheck()

Dim date1 As Date, date2 As Date, StartDate, EndDate

On Error GoTo Erro:

Dados.Activate
date1 = InputBox("Write the initial Date in format dd/mm/yyyy", "StartDate") 'incompatible value when user dont input a value. error 13
date2 = InputBox("Write the Final Date in format dd/mm/yyyy", "EndDate")

If date1 = "" Or date2 = "" Then
    Exit Sub
    End If
    
If Not IsDate(date1) Or Not IsDate(date2) Then
    MsgBox "Please enter a valid date format dd/mm/yyyy", vbCritical, "Invalid Date"
    
   ElseIf date1 > date2 Then

   MsgBox "The starter date must not be bigger than the final date ", vbCritical, "Invalid Date"
   ElseIf date1 > Date Then
   
    MsgBox "The starter Date must not be bigger than todays ", vbCritical, "Invalid Date"
   Else
    Dados.Activate
    StartDate = Format(DateValue(date1), "dd/mm/yyyy")
    EndDate = Format(DateValue(date2), "dd/mm/yyyy")
    Debug.Print StartDate
    Debug.Print EndDate
    
    ' Changing the static values to Variables returns nothing
      ActiveSheet.ListObjects("Dados").Range.AutoFilter Field:=3, _
                        Criteria1:=">=" & StartDate, _
                        Operator:=xlAnd, _
                        Criteria2:="<=" & EndDate
                        
     'must find a better way to write the code below
        Sheets("Sheet2").Select
        Range("C1").Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Clear
        Sheets("Dados").Select
        Range("A2:C2").Select
        Range(Selection, Selection.End(x1Down)).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range ("A1")
        ActiveSheet.Paste
        

     
   End If
   
   
Erro:
MsgBox "Something is wrong, contact the SystemAdm", vbCritical, "Check"
End Sub

CodePudding user response：

Here are some guidelines:

1. Always put “Option Explicit” at the beggining of the module. You can automatically set this: on the VBE, access Tools > Options > Editor and mark ‘Require Variable Declaration’ (PtBr: ‘Requerer declaração de variável’) Doing this way, the VBE force the DIM of the variables StartDate and EndDate . This will avoid misuse and mistakes with variables.
2. When checking dates, do not use “”, because VBE always will see this as an incompatible comparison. The sequent code, checking the two dates, do the job.
3. The filter Criteria strings need to be formed as this: Criteria1:=">=" & StartDate (...) Criteria2:="<=" & EndDate > also note that formatting them as "mm/dd/yyyy" in this case is required (here I did this way to run ok)
4. In case of mistakes entering the dates, offer a new try to the user, returning the process to the insertion of the two dates, using line labels. In addition, explicitly guide the user to left a blank date to abort the operation. Then the code will go to an Exit label (see below 5-b comment) .
5. In the final portion of the code you presumably want to copy the result data in the Auto-Filter on Sheet ‘Dados’ to the cell ‘A1’ on ‘Sheet2’. So, a “new version” of it could be this way:

• a) After the line ‘Selection.Clear’ put ‘Sheets("Dados").Select’.
  This was missing to redirect processing to the sheet “Dados”.

• b) Well, even if all it’s ok, at the end of execution the user will
  always receive the MsgBox alert. Insert “Exit Sub” before the label
  “Erro:” (you are a brazilian person – like me – Am I right?