In particular, we’ll focus on automating four essential date sequence activities:
| Scenario | Description |
|---|---|
| First Date of the Month | Extending dates to the first day of the month. |
| Last Date of the Month | Extending dates to the last day of the month. |
| Last Business Date of the Month | Extending dates to the last business day of the month. |
| Second Last Business Date of Month | Extending dates to the second last business day of the month. |
Description of common variable declaration and initial validation used in all the above scenarios
1. **Variable Declarations:
**
- `FLD_Count`: Stores the number of months
to extend the dates.
- `i`: A loop counter.
- `LR`: Represents the last row number in
column A where a date is found.
- `LRDate`: Holds the date value found in
the last row of column A.
2. **Initial Validation:
**
- The below macro we used in all the scenarios, it first 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’s a starting point for the sequence.
If IsEmpty(ActiveSheet.Range("A2").Value) = True Then
MsgBox "Please Enter the Date in A2 Cell"
Else
"Remaining Part of Code to be Executed"
Scenario 1: First Date of the Month
This VBA macro is
designed to extend a sequence of dates in an Excel sheet by inserting the first
date of each subsequent month, starting from a given date.
```vba
Sub Start_Date_Month_A()
Dim FLD_Count As Integer, i
As Integer, LR As Long, LRDate As Date
If
IsEmpty(ActiveSheet.Range("A2").Value) = True 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
Month
For i = 1 To FLD_Count
Dim newDate As Date
newDate =
DateSerial(Year(LRDate), Month(LRDate) + i, 1)
ActiveSheet.Range("A" & LR + i).Value = newDate
Next i
End If
End Sub
Here’s a detailed
explanation of the code:
1. **Loop to Insert
Dates: **
- A `For` loop runs from 1 to `FLD_Count`.
For each iteration, the macro calculates a new date by using the `DateSerial`
function:
- `Year(LRDate)`: Extracts the year from
the last date in column A.
- `Month(LRDate) + i`: Advances the month
by `i` months.
- `1`: Sets the day to the first day of
the month.
- The calculated `newDate` is then placed in
the next available row in column A, starting from `LR + 1`.
2. **Result: **
- The macro effectively populates column A
with the first date of each month for the number of months specified, extending
the date sequence forward by `FLD_Count` months from the last date found in
column A.
Scenario 2: Last Date of the Month
This VBA macro,
named `End_Date_Month_A`, is designed to extend a sequence of dates in an Excel
worksheet by inserting the last date of each month for a specified number of
rows.
```vba
Sub End_Date_Month_A()
Dim FLD_Count As Integer, i
As Integer, LR As Long, LRDate As Date
If
IsEmpty(ActiveSheet.Range("A2").Value) = True 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
Month
For i = 1 To FLD_Count
LRDate =
DateSerial(Year(LRDate), Month(LRDate) + 1, 1)
Dim lastDay As Date
lastDay =
DateSerial(Year(LRDate), Month(LRDate) + 1, 0)
ActiveSheet.Range("A" & LR + i).Value = lastDay
Next i
End If
End Sub
1. **Insert Last Date of Each Month: **
For i = 1 To FLD_Count
LRDate =
DateSerial(Year(LRDate), Month(LRDate) + 1, 1)
Dim lastDay As Date
lastDay =
DateSerial(Year(LRDate), Month(LRDate) + 1, 0)
ActiveSheet.Range("A" & LR + i).Value = lastDay
Next i
- The loop runs from 1 to `FLD_Count`, which determines how many rows will be updated.
- `LRDate` is updated to the first day of the next month using `DateSerial`.
- `lastDay` calculates the last day of the current month by setting the day parameter to 0, which effectively moves back one day from the first day of the next month.
- The calculated `lastDay` is then inserted into the next row in column A.
2. **End of the Macro:
**
The loop continues until all specified rows
are filled with the last date of each month.
Scenario 3: Last Business Date of the Month
The VBA macro
`Extend_Last_Business_Date_A` is designed to extend a sequence of dates by
inserting the last business day of each month into a specified range in an
Excel worksheet.
Code
```vba
Sub Extend_Last_Business_Date_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 Business Date of Month
For i = 1 To FLD_Count
LRDate = DateSerial(Year(LRDate),
Month(LRDate) + 1, 1)
Dim lastDay As Date
lastDay =
Application.WorksheetFunction.WorkDay(Application.WorksheetFunction.EoMonth(LRDate,
0) + 1, -1)
ActiveSheet.Range("A"
& LR + i).Value = lastDay
Next i
End If
End Sub
Here’s a detailed explanation of the code:
1. **Date Extension
Loop**:
For i = 1 To FLD_Count
LRDate =
DateSerial(Year(LRDate), Month(LRDate) + 1, 1)
Dim lastDay As Date
lastDay =
Application.WorksheetFunction.WorkDay(Application.WorksheetFunction.EoMonth(LRDate,
0) + 1, -1)
ActiveSheet.Range("A" & LR + i).Value = lastDay
Next i
- The loop runs from 1 to `FLD_Count`,
extending the date sequence.
- `LRDate` is updated to the first day of
the next month using `DateSerial`.
- `lastDay` calculates the last business day
of that month:
-
`Application.WorksheetFunction.EoMonth(LRDate, 0)` returns the last day of the
current month.
- `Application.WorksheetFunction.WorkDay(..., -1)` adjusts to the last business day before this date.
- The `lastDay` value is then placed in the next available row in column A.
2. **End of the Macro: **
The loop continues until all specified rows are filled with the last business date of each month.
Scenario 4: Second Last Business Date of the Month
The VBA macro
`Extend_Last_Business_Date_A` is designed to extend a sequence of dates by
inserting the last business day of the month based on a given date.
```vba
Sub Extend_SecondLast_Business_Date_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 Second Last
Business Date of Month
For i = 1 To FLD_Count
LRDate =
DateSerial(Year(LRDate), Month(LRDate) + 1, 1)
Dim lastDay As Date
lastDay =
Application.WorksheetFunction.WorkDay(Application.WorksheetFunction.EoMonth(LRDate,
0) + 1, -2)
ActiveSheet.Range("A" & LR + i).Value = lastDay
Next i
End If
End Sub
1. **Loop to Insert
Last Business Date of Each Month: **
For i = 1 To FLD_Count
LRDate =
DateSerial(Year(LRDate), Month(LRDate) + 1, 1)
Dim lastDay As Date
lastDay =
Application.WorksheetFunction.WorkDay(Application.WorksheetFunction.EoMonth(LRDate,
0) + 1, -1)
ActiveSheet.Range("A" & LR + i).Value = lastDay
Next i
- `LRDate = DateSerial(Year(LRDate),
Month(LRDate) + 1, 1)` calculates the first day of the next month.
- `lastDay` is determined by finding the last business day of the current month.
-`Application.WorksheetFunction.EoMonth(LRDate, 0)` gives the last day of the month, and
-`Application.WorksheetFunction.WorkDay(..., -1)` finds the last
business day before that date.
- **Insert Date:**
- `ActiveSheet.Range("A" &
LR + i).Value = lastDay` inserts the calculated last business day into the next
row in column A.
2. **End of the
Subroutine:**
The loop continues until all specified rows are filled with the second last business date of each month.
By mastering these VBA techniques, you can eliminate the need for manual date calculations, reduce errors, and streamline your workflow, allowing you to focus on more strategic tasks.
Comments
Post a Comment