EXtremly Urgent Please

  Voted   Clear

Hi guys!

I was wondering if it was possible on Excel to set up a formula that would automatically update when a contract is ending.

For example one contract has a 6 months contract that starts at 1/1/2018 and must end at 31/6/2018..So the contract should roll over and automatically renews on a monthly .Also I have some contracts starting mid of every months.

Is there a way to set up the formula where it would update the Current End Date to New start date ? 

We have a lot of contracts and it would be really helpful if it would update itself automatically, so we wouldn't miss when a contract is coming due.



Stop using "EXtremly Urgent Please" as your title. The title must describe the question.
don (rep: 1685) Dec 19, '17 at 11:02 am
Add to Discussion



Please paste the code below in the 'ThisWorkbook' module of your VBA project. (Open the VB Editor by pressing Alt+F11), then save the workbook as macro-enabled. The contract dates will be updated when you next open the workbook.

Option Explicit
Private Enum Nws                ' Worksheet navigation
                                ' change the numbers here
                                ' f.i. NwsStart = 7 changes value to column G
                                ' missing value means "previous + 1
                                ' therefore NwsStart is now 5 and NwsEnd is now 6
    ' 18 Dec 2017
    NwsFirstRow = 3
    NwsTerm                     ' = 4, identifies column D
    NwsStart                    ' identifies column E
End Enum
Private Sub Workbook_Open()
    ' 18 Dec 2017
    Dim StartDate As Variant
    Dim EndDate As Variant
    Dim Term As Integer
    Dim Rl As Long              ' last used row
    Dim R As Long
    Application.ScreenUpdating = False
    With Sheet1                 ' change this if you use another sheet
        Rl = .Cells(.Rows.Count, NwsTerm).End(xlUp).Row
        For R = NwsFirstRow To Rl
            EndDate = .Cells(R, NwsEnd).Value
            If IsDate(EndDate) Or (Len(Trim(EndDate)) = 0) Then
                If CLng(EndDate) <= CLng(Date) Then
                    StartDate = .Cells(R, NwsStart).Value
                    If IsDate(StartDate) Then
                        Term = Int(Val(.Cells(R, NwsTerm)))
                        If Term Then
                            If CLng(EndDate) Then StartDate = EndDate
                            EndDate = DateAdd("m", Term, StartDate)
                            .Cells(R, NwsStart).Value = StartDate
                            .Cells(R, NwsEnd).Value = EndDate
                            MsgBox "No term has been set." & vbCr & _
                                   "I can't calculate contract" & vbCr & _
                                   "end in row " & R & ".", _
                                   vbInformation, "Missing contract data"
                        End If
                        WrongDate R, NwsStart
                    End If
                End If
                WrongDate R, NwsEnd
            End If
        Next R
    End With
    Application.ScreenUpdating = False
End Sub
Private Function WrongDate(ByVal R As Long, _
                           ByVal Clm As Nws)
    ' 18 Dec 2017
    Dim Typ As String
    Typ = IIf(Clm = NwsStart, "start", "end")
    MsgBox "Invalid " & Typ & " date in row " & R & ".", _
           vbInformation, "Data could not be read"
End Function

Do me a favour and amend the caption of your question to reflect the subject of your question. Thank you.



VB says Compile Error
Inside Enum Error
cannot find option for macro enabled
SHALI (rep: 2) Dec 19, '17 at 7:08 am
Move the Enum to a standard code module and remove the "Private" from its declaration line. Select xlsm as "SaveAs File Type" in the SaveAs dialog box (below the file name). Please replace your question's caption. You already have my attention, and "Very Urgent Please" is not a good title.
Variatus (rep: 2813) Dec 19, '17 at 9:33 pm
1 - Do I have to chnage enum to standard code throughout the code ?
2 - how to do it ?
3 - u mean cancel the word Private from the header of the code?
4 - We have contracts for different area,So can we upload the code on each     worksheet if Yes then how ?
  can u please explain what is to be done with the coding on new worksheets.I am uploading one please check.
I know its a bit annoying for u but,coding is difficult part for me.Sorry for the trouble.really appriciate your work & time.
SHALI (rep: 2) Dec 20, '17 at 1:04 am
Please change the caption of your question before you ask for more assistance from me. Thank you.
Variatus (rep: 2813) Dec 20, '17 at 1:56 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login