jeudi 29 mars 2018

How do I fix my if-statement that uses Date values and needs to be 20 years from the Current Date?

Being a newbie, any help is appreciated. I am trying to create a quick macro that:

1) Sets 2 variable in date format. 2) 1 variable cannot be more then twenty years from the current date. 3) Those variables then set another variable's cell values which are set to a numerical format.

I have included the macro below, it is not functional and I am getting issues with trying to code the worksheet as worksheet.

**Macro**
Sub Date_Check()

'Coding Variables

Dim WB As ThisWorkbook
Dim Test_Data As Sheet1
Dim statedate as String
statedate = Format(Date, "mm/dd/yyyy")
Dim enddate as string
enddate = Format(Date, "mm/dd/yyyy")
Dim todaydate as date
'Coverage date is enddate-statedate cell 
Dim CvgDate as Range
Set CvgDate(26) = enddate.value - statedate.value
Dim jj as Integer
Dim x as boolean
x = True

'set the ranges as the last cell to contain values

With Worksheet("TestData")
.Range("V2:V"). Offset(-1,0).xldown.Value2 = startdate
.Range("W2:W").Offset(-1,0).xldown.Value2 = enddate
.Range("FutureDate").Format(Date, "yyyy") = True

 'Create function that will check if range is 20 years within today's date.
FutureYear = 20  < DateSerial(Year, 1)
.Range("enddate").Offset(-1, 0).Select =x
.Cell.Number = "mm/dd/yyyy"

'Create an if statement using fuction
If Range("enddate").Cell <> = FutureYear Then
 MsgBox "Please check that the end reporting date is within 20 years from today's date!" 
   End If
 'Ensure that enddate meets criteria before being put in numerical format
.Range("Cvgdate").Offset(-1,0).Select
.Selection.NumberFormat = "jj"




End With

Any suggestions are helpful. But I am focused on ensuring that the enddate variable is within 20 years of the current date, but I cannot seem to use my code for the if-statement. I really appreciate the help and thanks in advance!

Aucun commentaire:

Enregistrer un commentaire