can this code be written better and optimised? If Not Intersect(Target, Range("F2")) Is…
- User
can this code be written better and optimised? If Not Intersect(Target, Range("F2")) Is Nothing Then GoTo COMPLETE End If
'CHECK IF CHANGE HAPPENED IN A2 If Not Intersect(Target, Range("A2")) Is Nothing Then 'Application.EnableEvents = False 'ActiveSheet.Calculate 'recalculate sheet 'Application.EnableEvents = True Application.Wait (Now + TimeValue("0:00:02")) 'ActiveSheet.Calculate 'recalculate sheet 'CHECK IF ADMIN REQUEST EXISTS Dim ws As Worksheet Dim adminRequestCell As Range Dim isAdminRequestPresent As Boolean
Set ws = Worksheets(Range("I2").Value) Set adminRequestCell = ws.Range("B:B").Find("Admin Request", , xlValues, xlWhole, xlByColumns) If Not adminRequestCell Is Nothing Then ' "Admin Request" is found in Column B in the Contractors Sheet isAdminRequestPresent = True answer = MsgBox(ADMIN_REQUEST_EXISTS_MSG, vbYesNo + vbQuestion, "Resend details") If answer = vbNo Then GoTo COMPLETE End If End If
If Not isAdminRequestPresent Then 'GoTo CHECKS End If
- Assistant
Yes, this code can be written better and optimized. Here are a few suggestions:
-
Avoid using GoTo statements as they can make the code harder to read and debug. Instead, use If-Else statements to control the flow of the code.
-
Instead of using Application.Wait to pause the code execution, consider using a Timer event or a Do While loop with a delay condition.
-
Use meaningful variable names instead of abbreviations, such as isAdminRequestPresent instead of isAdminReqPres.
-
Avoid using magic numbers and hard-coded values, such as "I2" and "Admin Request". Define constants or use named ranges instead.
-
Consider using early binding instead of late binding for object variables to improve performance.
-
Use error handling to handle possible run-time errors and prevent the code from crashing.
Based on these suggestions, the code could be rewritten as follows:
' Check if change happened in cell F2 If Target.Address = "2" Then GoTo COMPLETE ' Nothing to do here, so exit the sub End If
' Check if change happened in cell A2 If Target.Address = "2" Then ' Recalculate the sheet and wait for 2 seconds Application.EnableEvents = False ActiveSheet.Calculate Application.Wait Now + TimeValue("0:00:02") ActiveSheet.Calculate Application.EnableEvents = True
' Check if admin request exists Dim ws As Worksheet Dim adminRequestCell As Range Dim isAdminRequestPresent As Boolean Dim answer As VbMsgBoxResult Set ws = Worksheets("Contractors") Set adminRequestCell = ws.Range("B:B").Find(ADMIN_REQUEST_LABEL, , xlValues, xlWhole, xlByColumns) If Not adminRequestCell Is Nothing Then ' Admin request is found, ask user if they want to resend details isAdminRequestPresent = True answer = MsgBox(ADMIN_REQUEST_EXISTS_MSG, vbYesNo + vbQuestion, "Resend details") If answer = vbNo Then GoTo COMPLETE ' User doesn't want to resend details, so exit the sub End If End If If Not isAdminRequestPresent Then ' Continue with other checks End IfEnd If
' Other checks go here
COMPLETE: ' Cleanup code goes here
-
- User
Do my changes to the code do exactly the same thing? If Target.Address = "$F2" Then GoTo COMPLETE End If
If Target.Address = "A$2" Then Application.EnableEvents = False ActiveSheet.Calculate Application.EnableEvents = True Application.Wait Now + TimeValue("0:00:02")
Dim ws As Worksheet Dim adminRequestCell As Range Dim isAdminRequestPresent As Boolean Dim answer As VbMsgBoxResult
Set ws = Worksheets("Contractors") Set adminRequestCell = ws.Range("B:B").Find(ADMIN_REQUEST_LABEL, , xlValues, xlWhole, xlByColumns) If Not adminRequestCell Is Nothing Then isAdminRequestPresent = True answer = MsgBox(ADMIN_REQUEST_EXISTS_MSG, vbYesNo + vbQuestion, "Resend details") If Not isAdminRequestPresent Then GoTo COMPLETE End If End If End If End If
COMPLETE: ' Cleanup code goes here
- Assistant
No, your changes do not do exactly the same thing as the original code.
In your code, you have made two mistakes:
-
You have an extra End If statement that is not needed.
-
You have missed a part of the If statement condition. The original code checks for Target.Address = "2", but your code checks only for "A$2".
Here's the corrected code:
' Check if change happened in cell F2 If Target.Address = "2" Then GoTo COMPLETE ' Nothing to do here, so exit the sub End If
' Check if change happened in cell A2 If Target.Address = "2" Then ' Recalculate the sheet and wait for 2 seconds Application.EnableEvents = False ActiveSheet.Calculate Application.EnableEvents = True Application.Wait Now + TimeValue("0:00:02")
' Check if admin request exists Dim ws As Worksheet Dim adminRequestCell As Range Dim isAdminRequestPresent As Boolean Dim answer As VbMsgBoxResult Set ws = Worksheets("Contractors") Set adminRequestCell = ws.Range("B:B").Find(ADMIN_REQUEST_LABEL, , xlValues, xlWhole, xlByColumns) If Not adminRequestCell Is Nothing Then ' Admin request is found, ask user if they want to resend details isAdminRequestPresent = True answer = MsgBox(ADMIN_REQUEST_EXISTS_MSG, vbYesNo + vbQuestion, "Resend details") If answer = vbNo Then GoTo COMPLETE ' User doesn't want to resend details, so exit the sub End If Else ' Admin request is not found, so continue with other checks End IfEnd If
' Other checks go here
COMPLETE: ' Cleanup code goes here
-
- User
lastRow = wss.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1 ... what does this mean
- Assistant
lastRowis a variable that is being assigned the row number of the last non-empty cell in column B of thewssworksheet.Here's how the code works:
-
ws.Rows.Countreturns the total number of rows inwssworksheet. -
wss.Cells(ws.Rows.Count, "B")returns the last cell in column B ofwssworksheet. -
.End(xlUp)moves to the first non-empty cell above the last cell in column B, effectively finding the last non-empty cell in column B. -
.Rowreturns the row number of the last non-empty cell in column B. -
+ 1increments the row number by 1, so that the next available row is returned.
Thus, the final result is the row number of the next available row in column B of the
wssworksheet. This is typically used to add new data to the bottom of a range of data in Excel. -
- User
If I use the formula to find the last empty cell in row B and then apply a value how can I use offset in the formula to apply a value to row C on the same row
- Assistant
You can use the
Offsetmethod to apply a value to row C on the same row as the last empty cell in column B. Here's an example:Dim wss As Worksheet Set wss = Worksheets("MyWorksheet") 'Get the last empty row in column B Dim lastRow As Long lastRow = wss.Cells(wss.Rows.Count, "B").End(xlUp).Offset(1).Row 'Apply a value to column B on the last empty row wss.Cells(lastRow, "B").Value = "MyValue" 'Apply a value to column C on the same row as the last empty cell in column B wss.Cells(lastRow, "C").Value = "MyValue2"In the
lastRowvariable, theOffset(1)method is used to move one row down from the last non-empty cell in column B. This ensures that we are working with the first empty cell in column B. Then, theRowproperty is used to get the row number of the first empty cell.Then, we use the
Cellsproperty to apply a value to column B on the last empty row and to apply a value to column C on the same row as the last empty cell in column B. By using the samelastRowvariable as the parameter for the row number in bothCellsmethods, we ensure that the values are applied to the same row. - User
I am a bit confused here. If I use lastRow = wss.Cells(ws.Rows.Count, “B”).End(xlUp).Row + 1 to apply a value into the last row of column B then can I use lastRow = wss.Cells(ws.Rows.Count, “B”).End(xlUp).Offset(0,1).Row to apply a value to column C on the same row as my last entry in column B. I have removed the +1 from the formula and included an Offset
- Assistant
No, if you use
lastRow = wss.Cells(ws.Rows.Count, "B").End(xlUp).Offset(0,1).Row, it will give you the row number of the last non-empty cell in column C because you are offsetting to the right by one column.If you want to apply a value to column C on the same row as the last entry in column B, you can use the same
lastRowvariable that you already have and offset to the right by one column. Here's an example:Dim wss As Worksheet Set wss = Worksheets("MyWorksheet") 'Get the last empty row in column B Dim lastRow As Long lastRow = wss.Cells(wss.Rows.Count, "B").End(xlUp).Row + 1 'Apply a value to column B on the last empty row wss.Cells(lastRow, "B").Value = "MyValue" 'Apply a value to column C on the same row as the last empty cell in column B wss.Cells(lastRow, "C").Value = "MyValue2"In this example,
lastRowvariable is used to get the row number of the last empty cell in column B by adding 1 to the row number of the last non-empty cell in column B. Then, we useCellsmethod to apply a value to column B on the last empty row. Finally, we use the samelastRowvariable with an offset of 0 rows and 1 column to apply a value to column C on the same row as the last empty cell in column B. - User
will Value = Date add todays date to a cell
- Assistant
Yes,
Value = Datewill add today's date to a cell.Here's an example of how you can use this to add today's date to a cell:
Range("A1").Value = DateThis line of code sets the value of the cell in the first row and first column (i.e., cell A1) to the current date.
The
Datefunction returns the current system date, and assigning it to a cell using theValueproperty sets the cell's value to the current date. Note that the date is stored as a serial number in Excel, so the cell's number format will determine how the date is displayed. - User
Dim ws As Worksheet Dim adminRequestCell As Range Dim isAdminRequestPresent As Boolean Const ADMIN_REQUEST_EXISTS_MSG As String = "Admin Request has already been sent. Do you want to RE-SEND?" Set ws = Worksheets(Range("I2").Value) Set adminRequestCell = ws.Range("B:B").Find("Admin Request", , xlValues, xlWhole, xlByColumns)
If Not isAdminRequestPresent = False Then GoTo CHECKS ....... I am using this vba code to check if the value "Admin Request" exists in column B of the Worksheet named in cell I2. If the value does not exist, I want to GoTo CHECKS, if the value does exit then I want to GoTo COMPLETE. How do I complete the above code
- Assistant
You can modify your code as follows to achieve the desired behavior:
Dim ws As Worksheet Dim adminRequestCell As Range Dim isAdminRequestPresent As Boolean Const ADMIN_REQUEST_EXISTS_MSG As String = "Admin Request has already been sent. Do you want to RE-SEND?" Set ws = Worksheets(Range("I2").Value) Set adminRequestCell = ws.Range("B:B").Find("Admin Request", , xlValues, xlWhole, xlByColumns) If Not adminRequestCell Is Nothing Then ' "Admin Request" is found in Column B, so set flag to true isAdminRequestPresent = True ' Ask if admin request should be resent If MsgBox(ADMIN_REQUEST_EXISTS_MSG, vbQuestion + vbYesNo, "Resend Admin Request") = vbYes Then ' Resend admin request code here Else GoTo COMPLETE End If End If If Not isAdminRequestPresent Then GoTo CHECKS End If ' Additional code goes here COMPLETE: ' Code to execute when admin request exists and user chooses not to resend goes here ' ... Exit Sub CHECKS: ' Code to execute when admin request does not exist goes here ' ... Exit SubIn this modified code, if
adminRequestCellis not nothing, it means that "Admin Request" is found in column B. So, you set theisAdminRequestPresentflag to true and ask the user if the admin request should be resent.If the user clicks "Yes", you can put your code to resend the admin request here. If the user clicks "No", you can go to
COMPLETElabel to execute the code that should be executed when the admin request exists and the user chooses not to resend.If
adminRequestCellis nothing, it means that "Admin Request" is not found in column B. So, you canGoTo CHECKSlabel to execute the code that should be executed when the admin request does not exist.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.