Microsoft Office Forums - View Single Post - Add the value of a cell in the table to another cell

gmayor · 12-22-2021, 01:37 AM

Because of the Hijri date calculation, this is a task best performed using a macro and content controls. Save the attached as a macro enabled template and create a new document from it. The three content controls will be filled with the appropriate dates for the date the document is created. Note that your example appears to have the Hijri Date one day adrift according to the various on-line calculators, however this is not an area I am familiar with.
They code in the attached is as follows:

Code:

Option Explicit
'Graham Mayor - https://www.gmayor.com - Last updated - 22 Dec 2021 
Sub AutoNew()
    AddDates
End Sub

Sub AddDates()
Dim oCC As ContentControl
    For Each oCC In ActiveDocument.ContentControls
        Select Case LCase(oCC.Title)
            Case Is = "date"
                oCC.Range.Text = Date
            Case Is = "hijri date"
                oCC.Range.Text = dateHijri(Date)
            Case Is = "end date"
                oCC.Range.Text = Format((Date + 3), "yyyy/mm/dd")
            Case Else
        End Select
    Next oCC
End Sub

Private Function dateHijri(sDate As String) As String
Dim vVal As Variant
Dim dtHijri As Date
    VBA.Calendar = vbCalGreg
    If sDate <> vbNullString Then
        On Error GoTo lbl_Exit
        dtHijri = DateValue(sDate)
        VBA.Calendar = vbCalHijri
        dateHijri = dtHijri
    End If
    VBA.Calendar = vbCalGreg
    Exit Function
lbl_Exit:
    dateHijri = vbNullString
    VBA.Calendar = vbCalGreg
End Function