| Scenario | Description |
|---|---|
| First Date of the Year | Extending dates to the first day of the year |
| Last Date of Year | Extending dates to the last day of the year |
| Last Business Date of the Year | Extending dates to the last business day of the year. |
Scenario 1: First Date of the Year
The `Extend_First_Date_Year_A` subroutine is designed to extend a date sequence by adding the first date of subsequent years to a specified column in an Excel worksheet.
Code
```vba
Sub Extend_First_Date_Year_A()
Dim FLD_Count As Integer, i
As Integer, LR As Long, LRDate As Date
If
IsEmpty(ActiveSheet.Range("A2").Value) Then
MsgBox "Please
Enter the Date in A2 Cell"
Else
FLD_Count =
ActiveSheet.Range("C2").Value
LR =
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LRDate =
ActiveSheet.Range("A" & LR).Value
' Insert First Date of
Year after the Last Added Date
Dim firstDateOfYear As
Date
firstDateOfYear =
DateSerial(Year(LRDate) + 1, 1, 1)
For i = 1 To FLD_Count
ActiveSheet.Range("A" & LR + i).Value = firstDateOfYear
firstDateOfYear =
DateSerial(Year(firstDateOfYear) + 1, 1, 1)
Next i
End If
End Sub
Code Explanation:
1. **Variable Declarations: **
- `FLD_Count` (Integer): Stores the number of additional last dates of the year to add.
- `i` (Integer): Used as a counter in the loop.
- `LR` (Long): Stores the row number of the last non-empty cell in column A.
- `LRDate` (Date): Stores the date in the last non-empty cell in column A.
2. **Check for Initial Date in Cell A2:**
```vba
If IsEmpty(ActiveSheet.Range("A2").Value) Then
MsgBox "Please Enter the Date in A2 Cell"
```
- This condition checks if cell A2 is empty. If it is, a message box prompts the user to enter a date in cell A2. This ensures that there is an initial date to work with.
3. **Retrieve Values: **
```vba
FLD_Count = ActiveSheet.Range("C2").Value
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LRDate = ActiveSheet.Range("A" & LR).Value
```
- `FLD_Count`: Reads the number of years to extend from cell C2.
- `LR`: Finds the last row in column A that contains a date.
- `LRDate`: Retrieves the date from the last row in column A.
Dim firstDateOfYear As
Date
firstDateOfYear =
DateSerial(Year(LRDate) + 1, 1, 1)
-
**firstDateOfYear**: A date variable that calculates the first day of the next
year based on the last date found in column A. The `DateSerial` function is
used to create this date.
For i = 1 To FLD_Count
ActiveSheet.Range("A" & LR + i).Value = firstDateOfYear
firstDateOfYear =
DateSerial(Year(firstDateOfYear) + 1, 1, 1)
Next i
- A loop is
initiated to run from 1 to the value of `FLD_Count`.
- Inside the loop:
- The first day of the next year is placed in
the cell below the last used row in column A.
- The `firstDateOfYear` is then updated to
the first day of the following year.
5. **Result:**
- This process
repeats until the loop completes, effectively adding the first day of each
subsequent year in column A for the specified number of years.
Scenario 2: Last Date of the Year
This VBA macro is designed to extend a sequence of dates by adding a specified number of dates that represent the last date of each year, starting from the last date already present in the sequence.
Code
```vba
Sub Extend_Last_Date_Year_A()
Dim FLD_Count As Integer, i
As Integer, LR As Long, LRDate As Date
If
IsEmpty(ActiveSheet.Range("A2").Value) Then
MsgBox "Please
Enter the Date in A2 Cell"
Else
FLD_Count =
ActiveSheet.Range("C2").Value
LR =
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LRDate =
ActiveSheet.Range("A" & LR).Value
' Insert Last Date of
Year after the Last Added Date
Dim firstDateOfYear As
Date
firstDateOfYear =
DateSerial(Year(LRDate) + 1, 12, 31)
For i = 1 To FLD_Count
ActiveSheet.Range("A"
& LR + i).Value = firstDateOfYear
firstDateOfYear =
DateSerial(Year(firstDateOfYear) + 1, 12, 31)
Next i
End If
End Sub
4. **Initialize the
First Last Date of Year:**
Dim firstDateOfYear As Date
firstDateOfYear = DateSerial(Year(LRDate) + 1, 12, 31)
-`firstDateOfYear` is set to the last date
of the year following the year of the last date found in column A.
5. **Loop to Insert
Dates:**
For i = 1 To FLD_Count
ActiveSheet.Range("A" & LR + i).Value = firstDateOfYear
firstDateOfYear =
DateSerial(Year(firstDateOfYear) + 1, 12, 31)
Next i
- The loop runs from `1` to `FLD_Count`, inserting the calculated last date of each subsequent year into the rows following the last date found in column A.
- After each insertion, `firstDateOfYear` is updated to the last date of the next year, ready for the next loop iteration.
6. **Result:**
Scenario 3: Last Business Date of the Year
Code
```vba
Sub Extend_Last_Business_Date_Year_A()
Dim FLD_Count As Integer, i
As Integer, LR As Long, LRDate As Date
If
IsEmpty(ActiveSheet.Range("A2").Value) Then
MsgBox "Please
Enter the Date in A2 Cell"
Else
FLD_Count =
ActiveSheet.Range("C2").Value
LR =
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LRDate =
ActiveSheet.Range("A" & LR).Value
Dim nextYear As Integer
nextYear = Year(LRDate)
+ 1
' Insert Last Business
Date of Year after the Last Added Date
For i = 1 To FLD_Count
LR = LR + 1
ActiveSheet.Range("A" & LR).Value =
WorksheetFunction.WorkDay(DateSerial(nextYear + 1, 1, 1), -1)
nextYear = nextYear
+ 1
Next i
End If
End Sub
Code Explanation:
1. **Variable
Declaration:**
```vba
Dim FLD_Count As Integer, i As Integer, LR
As Long, LRDate As Date
```
- `FLD_Count`: Stores the number of years
you want to extend the date sequence.
- `i`: Used as a counter in the loop.
- `LR`: Stores the last row in column A
where a date is found.
- `LRDate`: Stores the date value from the
last row in column A.
2. **Checking for
Input in Cell A2:**
```vba
If
IsEmpty(ActiveSheet.Range("A2").Value) Then
MsgBox "Please Enter the Date in A2
Cell"
```
- The code first checks if cell A2 is empty.
If it is, a message box prompts the user to enter a date in A2. This ensures
that the subroutine has a starting date to work with.
3. **Initial
Setup:**
```vba
FLD_Count =
ActiveSheet.Range("C2").Value
LR = ActiveSheet.Range("A" &
Rows.Count).End(xlUp).Row
LRDate = ActiveSheet.Range("A"
& LR).Value
```
- `FLD_Count` is assigned the value from
cell C2, which determines how many years the dates should be extended.
- `LR` finds the last row in column A that
contains a date by starting from the bottom and moving up.
- `LRDate` stores the date from the last row
found in column A.
4. **Calculating
the Next Year:**
```vba
Dim nextYear As Integer
nextYear = Year(LRDate) + 1
```
-`nextYear` is set to the year following
`LRDate`. This will be the year for which the last business date will be
calculated.
5. **Loop to Extend
Dates:**
For i = 1 To FLD_Count
LR = LR + 1
ActiveSheet.Range("A" & LR).Value =
WorksheetFunction.WorkDay(DateSerial(nextYear + 1, 1, 1), -1)
nextYear = nextYear + 1
Next i
- The loop runs from 1 to `FLD_Count`,
extending the date sequence by the specified number of years.
- Within the loop:
- `LR` is incremented to move to the next
row in column A.
- The last business day of the year
(`nextYear`) is calculated using the `WorkDay` function:
- `DateSerial(nextYear + 1, 1, 1)`
generates January 1st of the year after `nextYear`.
- The `WorkDay` function then steps back
one business day (`-1`) to find the last business day of `nextYear`.
- The result is placed in the next
available row in column A.
- `nextYear` is then incremented by 1 for
the next iteration of the loop.
6. **Completion:**
- The loop continues until the dates are
extended for the number of years specified in `C2`.
- C2 value can be vary depending upon the requirement, you can assign any value to Cell C2
Comments
Post a Comment