In this VBA code, when I doubleclick on a cell in column H, it is copying the wrong row…
- User
In this VBA code, when I doubleclick on a cell in column H, it is copying the wrong row from sheet 'Key Allocation' to sheet 'Key Return'
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ThisWorkbook.Worksheets("Key Allocation").Unprotect password:="edit" ThisWorkbook.Worksheets("Key Return").Unprotect password:="edit" ThisWorkbook.Worksheets("Key Audit").Unprotect password:="edit"
Application.EnableEvents = False
If Target.Address = "1" Then Cancel = True ' Prevent edit mode for I1 ' Clear specific ranges Range("A2:H2").ClearContents Range("I3").ClearContents Range("I4").ClearContents Range("A4:H300").ClearContents MsgBox "Page has been reset" End If
If Target.Column <> 8 Or Target.row < 4 Then Exit Sub ' Check if double clicked in column H and exit if not If Target.Column = 8 Then ' Rest code for processing row in column H ' Set auditRow based on the double-clicked cell in column H Set auditRow = Sheets("Key Audit").Rows(Target.row).Range("A" & Target.row & ":H" & Target.row) Dim returnSheet As Worksheet, allocSheet As Worksheet, auditSheet As Worksheet Set returnSheet = Sheets("Key Return") Set allocSheet = Sheets("Key Allocation") Set auditSheet = Sheets("Key Audit") ' Confirmation message If MsgBox("Would you like to record details of a Lost or Returned Key?", vbYesNo) = vbNo Then Application.EnableEvents = True ThisWorkbook.Worksheets("Key Audit").Protect password:="edit" Exit Sub End If
' Password storage cell Dim passwordCell As Range Set passwordCell = Range("J1") ' Password Retrieval Dim storedPassword As String storedPassword = passwordCell.Value ' Password Prompt Dim enteredPassword As String enteredPassword = InputBox("Enter Password to access Key Return sheet:", "Password Required") ' Check entered password If enteredPassword <> storedPassword Then MsgBox "Incorrect Password. Access Denied.", vbExclamation Application.EnableEvents = True ThisWorkbook.Worksheets("Key Audit").Protect password:="edit" Exit Sub End If
' Find matching row in "Key Allocation" sheet Set allocRow = allocSheet.Rows.Find(auditRow.Range("A1:H1"), LookIn:=xlValues, LookAt:=xlWhole)
' Check if matching row found If allocRow Is Nothing Then MsgBox "Matching row not found in 'Key Allocation' sheet.", vbExclamation Application.EnableEvents = True ThisWorkbook.Worksheets("Key Audit").Protect password:="edit" Exit Sub End If
allocSheet.Visible = True ' Copy specific range (A:H) from "Key Allocation" to "Key Return" allocRow.EntireRow.Copy returnSheet.Rows(returnSheet.Rows.Count).End(xlUp).Offset(1).EntireRow ' Delete matching row from "Key Allocation" sheet allocRow.EntireRow.Delete Shift:=xlUp allocSheet.Visible = False Application.Wait Now + TimeValue("00:00:01") ' Unhide and activate "Key Return" sheet (using Visible property - recommended for all versions) returnSheet.Visible = True ' OR use Option 2 or 3 based on your Excel version (see previous comments) returnSheet.Activate End If
Dim auditSheetRow As Long Dim resetValue As Variant Dim cell As Range Dim i As Integer ' Clear range A4:H30 in auditSheet before copying auditSheet.Range("A4:H300").ClearContents resetValue = auditSheet.Range("A2").Value auditSheetRow = auditSheet.Cells(auditSheet.Rows.Count, 1).End(xlUp).row + 1 For Each cell In allocSheet.Range("A2:A" & allocSheet.Cells(allocSheet.Rows.Count, 1).End(xlUp).row) If cell.Value = resetValue And allocSheet.Cells(cell.row, 8).Value <> Date Then With auditSheet.Rows(auditSheetRow) For i = 1 To 8 ' Copying only columns A to H If i <> 9 Then ' Excluding column I .Cells(1, i).Value = cell.EntireRow.Cells(1, i).Value End If Next i End With auditSheetRow = auditSheetRow + 1 End If Next cell Application.EnableEvents = True ThisWorkbook.Worksheets("Key Audit").Unprotect password:="edit" ThisWorkbook.Worksheets("Key Return").Unprotect password:="edit" ThisWorkbook.Worksheets("Key Allocation").Unprotect password:="edit" Cancel = True ' Prevent edit modeEnd Sub
- Assistant
In the code provided, it seems that the issue may be related to how the matching row is being found in the "Key Allocation" sheet. The Find method is being used with the entire row range (A1:H1) as the search criteria, which may not be identifying the correct row.
One way to ensure the correct row is found is to concatenate all the values in the auditRow range and then search for that concatenated value in the "Key Allocation" sheet. This way, you can ensure that all values in the row are considered for the match.
Here's how you can modify the code to concatenate the values in auditRow and search for the matching row:
' Find matching row in "Key Allocation" sheet Dim concatValue As String concatValue = "" For Each cell In auditRow concatValue = concatValue & cell.Value & "/" Next cell Set allocRow = allocSheet.Rows.Find(concatValue, LookIn:=xlValues, LookAt:=xlWhole)By concatenating all the values in the auditRow range and then searching for that concatenated value in the "Key Allocation" sheet, you can ensure that the correct row is identified for copying to the "Key Return" sheet.
- User
variable not defined: cell
- Assistant
Apologies for the oversight. To correct the "variable not defined: cell" error, you need to declare the "cell" variable before using it in the loop. You can declare it as a Range variable at the beginning of the subroutine.
Here's the updated code snippet with the "cell" variable declared:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) ThisWorkbook.Worksheets("Key Allocation").Unprotect password:="edit" ThisWorkbook.Worksheets("Key Return").Unprotect password:="edit" ThisWorkbook.Worksheets("Key Audit").Unprotect password:="edit" Application.EnableEvents = False If Target.Address = "$I$1" Then Cancel = True ' Prevent edit mode for I1 ' Clear specific ranges Range("A2:H2").ClearContents Range("I3").ClearContents Range("I4").ClearContents Range("A4:H300").ClearContents MsgBox "Page has been reset" End If If Target.Column <> 8 Or Target.Row < 4 Then Exit Sub ' Check if double-clicked in column H and exit if not If Target.Column = 8 Then ' Rest code for processing row in column H ' Set auditRow based on the double-clicked cell in column H Dim auditRow As Range Set auditRow = Sheets("Key Audit").Rows(Target.Row).Range("A" & Target.Row & ":H" & Target.Row) Dim returnSheet As Worksheet, allocSheet As Worksheet, auditSheet As Worksheet Set returnSheet = Sheets("Key Return") Set allocSheet = Sheets("Key Allocation") Set auditSheet = Sheets("Key Audit") ' Additional code for concatenating values in auditRow and finding matching row in "Key Allocation" sheet Dim cell As Range Dim concatValue As String concatValue = "" For Each cell In auditRow concatValue = concatValue & cell.Value & "/" Next cell ' Find matching row in "Key Allocation" sheet using concatenated value Set allocRow = allocSheet.Rows.Find(concatValue, LookIn:=xlValues, LookAt:=xlWhole) ' Rest of the code continues here... End If ' Other code within the subroutine... Application.EnableEvents = True ThisWorkbook.Worksheets("Key Audit").Protect password:="edit" Cancel = True ' Prevent edit mode End SubBy declaring the "cell" variable as a Range variable at the beginning of the subroutine, you should no longer encounter the "variable not defined" error.
- User
the code is not finding the matching row
- Assistant
If the code is still not finding the matching row after concatenating the values and searching in the "Key Allocation" sheet, you may need to troubleshoot the issue further. Here are a few steps you can take to diagnose and fix the problem:
-
Check Concatenated Value: Verify that the concatenated value being used for the search is correct. You can add a
Debug.Print concatValuestatement after the concatenation loop to check the concatenated value in the Immediate Window (Ctrl + G) while debugging. -
Debugging Find Method: After finding the allocRow using the Find method, you can add a check to see if the allocRow is Nothing or if the correct row was actually found. You can use the following code snippet for debugging:
If allocRow Is Nothing Then MsgBox "Matching row not found in 'Key Allocation' sheet. Concatenated Value: " & concatValue, vbExclamation Application.EnableEvents = True ThisWorkbook.Worksheets("Key Audit").Protect password:="edit" Exit Sub End If- Modify Search Criteria: If the concatenated search is still not working, you can try modifying the search criteria or approach. Instead of concatenating all values with "/", you can try different concatenation approaches or search criteria that better match the data in the "Key Allocation" sheet.
By carefully checking the concatenated value, debugging the Find method, and experimenting with different search criteria, you should be able to determine why the matching row is not being found and make the necessary adjustments to fix the issue.
-