Can you please describe waht this formula does: =IF(INDIRECT($AB$2)<>"", TODAY() -…
- User
Can you please describe waht this formula does: =IF(INDIRECT(2)<>"", TODAY() - INDIRECT(2), "")
- Assistant
Certainly! Let's break down the formula step by step:
Explanation of the Formula
-
INDIRECT(2):
- This part uses the
INDIRECTfunction to reference a cell specified in cellAB2. TheINDIRECTfunction converts a text string into a cell reference. For example, ifAB2contains the text "C5", thenINDIRECT($AB$2)will refer to the content of cellC5.
- This part uses the
-
INDIRECT(2)<>"":
- This checks if the value obtained from
INDIRECT($AB$2)is not equal to an empty string. In essence, it checks if the referenced cell (for example,C5ifAB2had "C5") contains any value.
- This checks if the value obtained from
-
TODAY() - INDIRECT(2):
- If the condition in the previous step is true (meaning the referenced cell is not empty), this part calculates the difference between the current date (obtained using
TODAY()) and the value in the referenced cell (INDIRECT($AB$2)). For this to work correctly, the value in that cell should typically represent a date. The result will be the number of days between today and the date in the referenced cell.
- If the condition in the previous step is true (meaning the referenced cell is not empty), this part calculates the difference between the current date (obtained using
-
"":
- If the cell referenced by
INDIRECT($AB$2)is empty, the formula returns an empty string instead of a number.
- If the cell referenced by
Summary
Putting it all together, the formula:
- Checks if a cell referenced by the address in
AB2is not empty. - If that cell contains a date, it calculates and returns the number of days between today and the date in that cell.
- If that cell is empty, it returns an empty string.
Example
- If
AB2contained "C5", andC5had the date2023-10-01, then the formula would return the number of days from2023-10-01to today (e.g., October 26, 2023, would result in 25). - If
C5were empty, the formula would return an empty string ("").
-
- User
Can you write me a VBA code that will do the following:
In sheet 'Day' column G2 downwards, check if the date is 1 days before Today In sheet 'WeeK' column G2 downwards, check if the date is 3 days before Today In sheet 'Fortnight' column G2 downwards, check if the date is 6 days before Today In sheet 'Month' column G2 downwards, check if the date is 12 days before Today In sheet 'QtrYear' column G2 downwards, check if the date is 24 days before Today In sheet 'HalfYear' column G2 downwards, check if the date is 48 days before Today In sheet 'Year' column G2 downwards, check if the date is 96 days before Today For each of the above once a balnk cell is found in column G exit loop. For each of value that matches the condition,
' Loop through each sheet For Each ws In ThisWorkbook.Worksheets ' Check if the sheet is one of the specified sheets If ws.Name = "Day" Or ws.Name = "Week" Or ws.Name = "Fortnight" Or ws.Name = "Month" Or ws.Name = "QtrYear" Or ws.Name = "HalfYear" Or ws.Name = "Year" Then
Store the values of column G in an array if the value is a date
Dim rngF As Range Dim arrF As Variant Set rngF = ws.Range("G2:G40") arrF = rngF.ValueFind the value in the array that match the condition specified above
Build the result string with sheet name and value of column A result = ws.Name & " Task: " & ws.Range("A" & lastRow).Value
' Determine the next empty row in column H (and I) With Sheets("Planner") Dim nextRow As Long nextRow = .Range("H" & .Rows.count).End(xlUp).row + 1Create a header that says "Tasks Nearing their Due Dates"
Then Paste the result in sheet "Planner" column H after the header .Range("H" & nextRow).Value = result
' Paste the address formula in column I as text .Range("I" & nextRow).Value = "'" & ws.Name & "!" & ws.Range("F" & lastRow).Address ' Leading apostrophe to make it text End With End If Next i End If Next ws - Assistant
Certainly! Below is a VBA code that fulfills your requirements. This code goes through each specified worksheet, checks the relevant conditions for dates in column G, and captures the matching results into the "Planner" sheet while ensuring that it only processes until it reaches a blank cell in column G of each sheet.
Sub CheckDueDates() Dim ws As Worksheet Dim rngF As Range Dim arrF As Variant Dim lastRow As Long, nextRow As Long Dim result As String Dim todayDate As Date Dim checkDate As Date Dim i As Long todayDate = Date ' Get today's date ' Loop through each specified sheet For Each ws In ThisWorkbook.Worksheets ' Check if the sheet is one of the specified sheets If ws.Name = "Day" Or ws.Name = "Week" Or ws.Name = "Fortnight" Or _ ws.Name = "Month" Or ws.Name = "QtrYear" Or ws.Name = "HalfYear" Or _ ws.Name = "Year" Then Set rngF = ws.Range("G2:G40") arrF = rngF.Value ' Loop through the array to check for conditions For i = 1 To UBound(arrF, 1) If IsDate(arrF(i, 1)) Then checkDate = CDate(arrF(i, 1)) ' Check conditions Select Case ws.Name Case "Day" If checkDate = todayDate - 1 Then lastRow = i + 1 ' Adjust for the loop index Case "Week" If checkDate = todayDate - 3 Then lastRow = i + 1 Case "Fortnight" If checkDate = todayDate - 6 Then lastRow = i + 1 Case "Month" If checkDate = todayDate - 12 Then lastRow = i + 1 Case "QtrYear" If checkDate = todayDate - 24 Then lastRow = i + 1 Case "HalfYear" If checkDate = todayDate - 48 Then lastRow = i + 1 Case "Year" If checkDate = todayDate - 96 Then lastRow = i + 1 End Select ' If a match is found, prepare to store the value If lastRow > 0 Then result = ws.Name & " Task: " & ws.Range("A" & lastRow).Value ' Determine the next empty row in Planner sheet column H With Sheets("Planner") nextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1 ' Create a header if it's the first entry If .Range("H1").Value = "" Then .Range("H1").Value = "Tasks Nearing their Due Dates" End If ' Paste the result in sheet "Planner" column H after the header .Range("H" & nextRow).Value = result ' Paste the address formula in column I as text .Range("I" & nextRow).Value = "'" & ws.Name & "!" & ws.Range("F" & lastRow).Address ' Leading apostrophe to make it text End With ' Reset lastRow for next matching cell lastRow = 0 End If ElseIf IsEmpty(arrF(i, 1)) Then ' Exit loop if we hit an empty cell Exit For End If Next i End If Next ws End SubExplanation of the Code:
-
Variable Declarations:
- The basic variables are declared at the start, including those for worksheets, range, and other necessary tracking.
-
Loop Through Specified Sheets:
- It specifically checks if the current worksheet's name matches any of the desired names.
-
Get Range Values:
- The code sets a range (G2:G40) for each relevant sheet and loads those values into an array for processing.
-
Date Checking Logic:
- A loop checks each cell in the specified range to see if it contains a date. Depending on the worksheet, it makes different comparisons with today's date.
-
Store Results:
- If a match is found, it constructs a result string using the worksheet name and value from column A, then moves to the "Planner" sheet to store the results.
-
Making it user-friendly:
- The header "Tasks Nearing their Due Dates" is added if it's the first entry.
-
Exit Conditions:
- If an empty cell is reached, the loop exits to prevent processing further.
Notes
- Ensure that the sheet names in the workbook match exactly those specified in the code.
- Run this code in the VBA editor to collect data based on specified conditions. You can modify it further as per your specific demands.
-
- User
Error: Case without Select Case, on the line: Case "Week"
- Assistant
The error you're encountering ("Case without Select Case") indicates that the
Select Casestructure isn't properly initialized. In the provided VBA code, theSelect Casestatement was mistakenly omitted at the beginning where thecasestatements are checking against the names of the sheets.Let's fix that by including the initial
Select Casestatement and cleaning up the logic. Here’s the corrected version of the code:Sub CheckDueDates() Dim ws As Worksheet Dim rngF As Range Dim arrF As Variant Dim lastRow As Long, nextRow As Long Dim result As String Dim todayDate As Date Dim checkDate As Date Dim i As Long todayDate = Date ' Get today's date ' Loop through each specified sheet For Each ws In ThisWorkbook.Worksheets ' Check if the sheet is one of the specified sheets If ws.Name = "Day" Or ws.Name = "Week" Or ws.Name = "Fortnight" Or _ ws.Name = "Month" Or ws.Name = "QtrYear" Or ws.Name = "HalfYear" Or _ ws.Name = "Year" Then Set rngF = ws.Range("G2:G40") arrF = rngF.Value ' Loop through the array to check for conditions For i = 1 To UBound(arrF, 1) If IsDate(arrF(i, 1)) Then checkDate = CDate(arrF(i, 1)) ' Check conditions using Select Case Select Case ws.Name Case "Day" If checkDate = todayDate - 1 Then lastRow = i + 1 ' Adjust for 1-based index End If Case "Week" If checkDate = todayDate - 3 Then lastRow = i + 1 End If Case "Fortnight" If checkDate = todayDate - 6 Then lastRow = i + 1 End If Case "Month" If checkDate = todayDate - 12 Then lastRow = i + 1 End If Case "QtrYear" If checkDate = todayDate - 24 Then lastRow = i + 1 End If Case "HalfYear" If checkDate = todayDate - 48 Then lastRow = i + 1 End If Case "Year" If checkDate = todayDate - 96 Then lastRow = i + 1 End If End Select ' If a match is found, prepare to store the value If lastRow > 0 Then result = ws.Name & " Task: " & ws.Range("A" & lastRow).Value ' Determine the next empty row in Planner sheet column H With Sheets("Planner") nextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1 ' Create a header if it's the first entry If .Range("H1").Value = "" Then .Range("H1").Value = "Tasks Nearing their Due Dates" End If ' Paste the result in sheet "Planner" column H after the header .Range("H" & nextRow).Value = result ' Paste the address formula in column I as text .Range("I" & nextRow).Value = "'" & ws.Name & "!" & ws.Range("F" & lastRow).Address ' Leading apostrophe to make it text End With ' Reset lastRow for next matching cell lastRow = 0 End If ElseIf IsEmpty(arrF(i, 1)) Then ' Exit loop if we hit an empty cell Exit For End If Next i End If Next ws End SubKey Corrections:
-
Initialization of Select Case:
- The
Select Casestatement is now properly initialized to cover the different cases based on the worksheet's name.
- The
-
Correct Logic Handling:
- Each case now correctly checks the date conditions without missing the case structure.
How It Works
- The code iterates over specified sheets to check if any date in column G matches the criteria. When a match is found, it records the information in the "Planner" sheet. It stops processing when it encounters an empty cell in column G.
Running the Code:
- Just follow these steps to run the code:
- Open Excel and press
ALT + F11to open the VBA editor. - Insert a new module by right-clicking on any item in the Project Explorer, going to Insert, and selecting Module.
- Copy and paste the corrected code into the new module.
- Close the VBA editor.
- Run the macro by pressing
ALT + F8, selectingCheckDueDates, and clicking Run.
- Open Excel and press
This should resolve the error and work as intended! If there are any other issues, feel free to ask.
-
- User
For checking the dates 'If checkDate = todayDate - 3 Then' , the = restricts it to a specific date. What I want is all dates 3 days before today
- Assistant
If you want to check whether the dates fall within a certain range (i.e., all dates that are 1 day, 3 days, etc., before today, rather than matching to a specific date), you’ll need to adjust the conditions in your code.
For example, if you want to include all dates from the specified days ago up to, but not including today, you will change your comparisons to check if
checkDatefalls within the acceptable range.Here’s how you can modify the code accordingly:
Sub CheckDueDates() Dim ws As Worksheet Dim rngF As Range Dim arrF As Variant Dim lastRow As Long, nextRow As Long Dim result As String Dim todayDate As Date Dim checkDate As Date Dim i As Long todayDate = Date ' Get today's date ' Loop through each specified sheet For Each ws In ThisWorkbook.Worksheets ' Check if the sheet is one of the specified sheets If ws.Name = "Day" Or ws.Name = "Week" Or ws.Name = "Fortnight" Or _ ws.Name = "Month" Or ws.Name = "QtrYear" Or ws.Name = "HalfYear" Or _ ws.Name = "Year" Then Set rngF = ws.Range("G2:G40") arrF = rngF.Value ' Loop through the array to check for conditions For i = 1 To UBound(arrF, 1) If IsDate(arrF(i, 1)) Then checkDate = CDate(arrF(i, 1)) ' Check conditions using Select Case Select Case ws.Name Case "Day" If checkDate = todayDate - 1 Then lastRow = i + 1 ' Adjust for 1-based index End If Case "Week" If checkDate >= todayDate - 3 And checkDate < todayDate Then lastRow = i + 1 End If Case "Fortnight" If checkDate >= todayDate - 6 And checkDate < todayDate Then lastRow = i + 1 End If Case "Month" If checkDate >= todayDate - 12 And checkDate < todayDate Then lastRow = i + 1 End If Case "QtrYear" If checkDate >= todayDate - 24 And checkDate < todayDate Then lastRow = i + 1 End If Case "HalfYear" If checkDate >= todayDate - 48 And checkDate < todayDate Then lastRow = i + 1 End If Case "Year" If checkDate >= todayDate - 96 And checkDate < todayDate Then lastRow = i + 1 End If End Select ' If a match is found, prepare to store the value If lastRow > 0 Then result = ws.Name & " Task: " & ws.Range("A" & lastRow).Value ' Determine the next empty row in Planner sheet column H With Sheets("Planner") nextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1 ' Create a header if it's the first entry If .Range("H1").Value = "" Then .Range("H1").Value = "Tasks Nearing their Due Dates" End If ' Paste the result in sheet "Planner" column H after the header .Range("H" & nextRow).Value = result ' Paste the address formula in column I as text .Range("I" & nextRow).Value = "'" & ws.Name & "!" & ws.Range("F" & lastRow).Address ' Leading apostrophe to make it text End With ' Reset lastRow for next matching cell lastRow = 0 End If ElseIf IsEmpty(arrF(i, 1)) Then ' Exit loop if we hit an empty cell Exit For End If Next i End If Next ws End SubChanges Made:
- Condition Adjustments:
- Instead of checking for equality, the conditions now check if
checkDatefalls within a specified range:- For "Week", the adjusted condition checks if
checkDateis greater than or equal totodayDate - 3and less thantodayDate. - Similar comparisons were applied to other cases for "Fortnight", "Month", "QtrYear", "HalfYear", and "Year".
- For "Week", the adjusted condition checks if
- Instead of checking for equality, the conditions now check if
How This Works:
- For the "Week" case:
- The check will pass for all dates that are 3 days before today up until the day before today.
- This logic is applied similarly to the other cases to encompass the specified time frames.
Running the Code:
- You can follow the same steps as before to run the modified code in Excel's VBA editor. The changes will ensure that all relevant dates within the specified range will be captured correctly. If you have any further tweaks or requirements, feel free to ask!
- Condition Adjustments:
- User
The code did not create the header
- Assistant
I apologize for the oversight. The header creation logic is designed to only place the header in cell
H1of the "Planner" sheet if that cell is currently empty. However, if any data has previously been added to the "Planner" sheet, the header would not be added again unless additional measures are taken.To ensure that the header is added only once when the macro is first run, you can check if the header is already present in
H1. If you want to guarantee that it displays every time you run the code, consider placing the header directly before any entries are added.Here is the revised code that ensures the header is created regardless of data presence:
Sub CheckDueDates() Dim ws As Worksheet Dim rngF As Range Dim arrF As Variant Dim lastRow As Long, nextRow As Long Dim result As String Dim todayDate As Date Dim checkDate As Date Dim i As Long todayDate = Date ' Get today's date ' Clear previous task entries in Planner sheet before adding new ones (optional) With Sheets("Planner") .Range("H2:I" & .Rows.Count).ClearContents ' Optional: Clear previous entries .Range("H1").Value = "Tasks Nearing their Due Dates" ' Always set this header End With ' Loop through each specified sheet For Each ws In ThisWorkbook.Worksheets ' Check if the sheet is one of the specified sheets If ws.Name = "Day" Or ws.Name = "Week" Or ws.Name = "Fortnight" Or _ ws.Name = "Month" Or ws.Name = "QtrYear" Or ws.Name = "HalfYear" Or _ ws.Name = "Year" Then Set rngF = ws.Range("G2:G40") arrF = rngF.Value ' Loop through the array to check for conditions For i = 1 To UBound(arrF, 1) If IsDate(arrF(i, 1)) Then checkDate = CDate(arrF(i, 1)) ' Check conditions using Select Case Select Case ws.Name Case "Day" If checkDate = todayDate - 1 Then lastRow = i + 1 ' Adjust for 1-based index End If Case "Week" If checkDate >= todayDate - 3 And checkDate < todayDate Then lastRow = i + 1 End If Case "Fortnight" If checkDate >= todayDate - 6 And checkDate < todayDate Then lastRow = i + 1 End If Case "Month" If checkDate >= todayDate - 12 And checkDate < todayDate Then lastRow = i + 1 End If Case "QtrYear" If checkDate >= todayDate - 24 And checkDate < todayDate Then lastRow = i + 1 End If Case "HalfYear" If checkDate >= todayDate - 48 And checkDate < todayDate Then lastRow = i + 1 End If Case "Year" If checkDate >= todayDate - 96 And checkDate < todayDate Then lastRow = i + 1 End If End Select ' If a match is found, prepare to store the value If lastRow > 0 Then result = ws.Name & " Task: " & ws.Range("A" & lastRow).Value ' Determine the next empty row in Planner sheet column H With Sheets("Planner") nextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1 ' Paste the result in sheet "Planner" column H after the header .Range("H" & nextRow).Value = result ' Paste the address formula in column I as text .Range("I" & nextRow).Value = "'" & ws.Name & "!" & ws.Range("F" & lastRow).Address ' Leading apostrophe to make it text End With ' Reset lastRow for next matching cell lastRow = 0 End If ElseIf IsEmpty(arrF(i, 1)) Then ' Exit loop if we hit an empty cell Exit For End If Next i End If Next ws End SubKey Changes:
-
Always Set Header:
- Instead of checking if
H1is empty, the headerTasks Nearing their Due Datesis set directly at the beginning of the procedure. This ensures the header is always created whenever the macro is run.
- Instead of checking if
-
Optional Clear Previous Contents:
- The code now includes lines to optionally clear previous data from the "Planner" sheet before adding new entries. Uncomment or adjust the
ClearContentsline as needed based on whether you want to keep old data or replace it.
- The code now includes lines to optionally clear previous data from the "Planner" sheet before adding new entries. Uncomment or adjust the
Running the Code:
- As in the previous instructions, you can run the modified code directly in the VBA editor. The header should now reliably be placed in cell
H1, allowing for clear understanding and easy reference of what data follows it. If you need further modifications or additions, feel free to ask!
-
- User
The Header must not be placed in H1. It must be placed in the first avalable cell in column H and the results to come after the header
- Assistant
To place the header in the first available cell in column H and ensure that results are added afterward, we can adjust the code accordingly. We will check for the first empty cell in column H and place the header there, followed by the results.
Here's the updated VBA code that accomplishes this:
Sub CheckDueDates() Dim ws As Worksheet Dim rngF As Range Dim arrF As Variant Dim lastRow As Long, nextRow As Long Dim result As String Dim todayDate As Date Dim checkDate As Date Dim i As Long todayDate = Date ' Get today's date ' Loop through each specified sheet For Each ws In ThisWorkbook.Worksheets ' Check if the sheet is one of the specified sheets If ws.Name = "Day" Or ws.Name = "Week" Or ws.Name = "Fortnight" Or _ ws.Name = "Month" Or ws.Name = "QtrYear" Or ws.Name = "HalfYear" Or _ ws.Name = "Year" Then Set rngF = ws.Range("G2:G40") arrF = rngF.Value ' Loop through the array to check for conditions For i = 1 To UBound(arrF, 1) If IsDate(arrF(i, 1)) Then checkDate = CDate(arrF(i, 1)) ' Check conditions using Select Case Select Case ws.Name Case "Day" If checkDate = todayDate - 1 Then lastRow = i + 1 ' Adjust for 1-based index End If Case "Week" If checkDate >= todayDate - 3 And checkDate < todayDate Then lastRow = i + 1 End If Case "Fortnight" If checkDate >= todayDate - 6 And checkDate < todayDate Then lastRow = i + 1 End If Case "Month" If checkDate >= todayDate - 12 And checkDate < todayDate Then lastRow = i + 1 End If Case "QtrYear" If checkDate >= todayDate - 24 And checkDate < todayDate Then lastRow = i + 1 End If Case "HalfYear" If checkDate >= todayDate - 48 And checkDate < todayDate Then lastRow = i + 1 End If Case "Year" If checkDate >= todayDate - 96 And checkDate < todayDate Then lastRow = i + 1 End If End Select ' If a match is found, prepare to store the value If lastRow > 0 Then result = ws.Name & " Task: " & ws.Range("A" & lastRow).Value ' Determine the next empty row in Planner sheet column H With Sheets("Planner") nextRow = .Range("H" & .Rows.Count).End(xlUp).Row + 1 ' Check if the first available cell is empty If nextRow = 1 Or .Range("H" & nextRow - 1).Value <> "" Then nextRow = nextRow ' Position in the next available row End If ' If it is the first entry, we need to insert the header first If .Range("H" & nextRow).Value = "" And .Range("H" & nextRow - 1).Value <> "" Then .Range("H" & nextRow).Value = "Tasks Nearing their Due Dates" nextRow = nextRow + 1 ' Move to next row after header for results End If ' Paste the result in sheet "Planner" column H after the header .Range("H" & nextRow).Value = result ' Optional: Paste the address formula in column I as text .Range("I" & nextRow).Value = "'" & ws.Name & "!" & ws.Range("F" & lastRow).Address ' Leading apostrophe to make it text End With ' Reset lastRow for next matching cell lastRow = 0 End If ElseIf IsEmpty(arrF(i, 1)) Then ' Exit loop if we hit an empty cell Exit For End If Next i End If Next ws End SubKey Changes:
-
Dynamic Header Placement:
- The header "Tasks Nearing their Due Dates" is placed in the first available empty cell in column H.
- If results are being added for the first time, the header will occupy the first empty cell in column H, and subsequent results will be placed in the row below.
-
Header Check Logic:
- After checking for the next available row, the code will check if it needs to insert the header before adding any results.
Running the Code:
- You can execute the updated code in the VBA editor, and it should correctly place the header in the first available cell in column H, followed by the results in subsequent rows. If you have further adjustments or requirements, let me know!
-