Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0125· OpenAIAi2 WildChat-4.8M (AI2)

In this VBA code, when I doubleclick on a cell in column H, it is copying the wrong row…

  1. 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 = "II1" 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 mode
    

    End Sub

  2. 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.

  3. User

    variable not defined: cell

  4. 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 Sub
    

    By declaring the "cell" variable as a Range variable at the beginning of the subroutine, you should no longer encounter the "variable not defined" error.

  5. User

    the code is not finding the matching row

  6. 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:

    1. Check Concatenated Value: Verify that the concatenated value being used for the search is correct. You can add a Debug.Print concatValue statement after the concatenation loop to check the concatenated value in the Immediate Window (Ctrl + G) while debugging.

    2. 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
    
    1. 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.