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

Please write me a VBA code to do the following; When I double click on a cell in column…

  1. User

    Please write me a VBA code to do the following;

    When I double click on a cell in column D, copy the value in column M to column G on the same row then in the next empty cell on the same row enter todays date in the following format dd/mm

  2. Assistant

    Sure! Here is a VBA code that will achieve the desired outcome:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim lastRow As Long
        Dim currentDate As String
        
        ' Check if double click occurred in column D
        If Target.Column = 4 Then
            ' Get the last used row in column D
            lastRow = Cells(Rows.Count, 4).End(xlUp).Row
            
            ' Copy value from column M to column G on the same row
            Cells(Target.Row, 7).Value = Cells(Target.Row, 13).Value
            
            ' Check if column M on the same row is not empty
            If Cells(Target.Row, 13).Value <> "" Then
                ' Get the current date
                currentDate = Format(Date, "dd/mm")
                
                ' Find the next empty cell in column G on the same row
                Cells(Target.Row, 7).End(xlToRight).Offset(0, 1).Value = currentDate
            End If
        End If
    End Sub
    

    Here's how to add this code in Excel:

    1. Press Alt + F11 to open the VBA editor.
    2. In the Project Explorer on the left side, find the worksheet where you want this code to work (e.g., Sheet1).
    3. Double-click on the worksheet name to open the code window.
    4. Paste the code into the code window.
    5. Close the VBA editor.

    Now, whenever you double-click a cell in column D, the value from column M will be copied to column G on the same row, and the current date will be entered in the next empty cell on the same row.

  3. User

    In the code below the following line 'Cells(Target.Row, 7).End(xlToRight).Offset(0, 1).Value = currentDate' is not doing exactly what I want. What I would like is for the code to determine which Target.Offset(0, ?), on the same row to the right is empty and then insert the current date in the format dd/mm.

    ' PSJulAug Private Sub PSJulAug(ByVal Target As Range) If Target.Offset(0, -2).Value = "-" Then MsgBox "Service Plan email has already been sent" ElseIf Target.Offset(0, -2).Value <> "-" Then Dim rng As Range Set rng = Intersect(Target, Me.Range("D:D")) Dim cell As Range For Each cell In rng Me.Cells(cell.Row, "B").Value = "-" Next cell

    Dim currentDate As String
    Target.Offset(0, 3).Value = Target.Offset(0, 9).Value currentDate = Format(Date, "dd/mm") Cells(Target.Row, 7).End(xlToRight).Offset(0, 1).Value = currentDate

        Call OpenPSJulAug
    End If
    doubleClickFlag = False
    

    End Sub

  4. Assistant

    To insert the current date in the next empty cell on the same row, you can use a loop to find the first empty cell in the range and then insert the current date into that cell. Here's an updated version of your code with the changes:

    Private Sub PSJulAug(ByVal Target As Range)
        If Target.Offset(0, -2).Value = "-" Then
            MsgBox "Service Plan email has already been sent"
        ElseIf Target.Offset(0, -2).Value <> "-" Then
            Dim rng As Range
            Dim currentDate As String
            Set rng = Intersect(Target, Me.Range("D:D"))
            
            Dim cell As Range
            For Each cell In rng
                Me.Cells(cell.Row, "B").Value = "-"
            Next cell
            
            Target.Offset(0, 3).Value = Target.Offset(0, 9).Value
            
            ' Find the first empty cell in column G on the same row
            Dim emptyCell As Range
            Set emptyCell = Cells(Target.Row, 7).End(xlToRight).Offset(0, 1)
            While Not IsEmpty(emptyCell)
                Set emptyCell = emptyCell.Offset(0, 1)
            Wend
            
            ' Insert the current date in the first empty cell
            currentDate = Format(Date, "dd/mm")
            emptyCell.Value = currentDate
            
            Call OpenPSJulAug
        End If
        doubleClickFlag = False
    End Sub
    

    This updated code will find the first empty cell in column G on the same row as the double-clicked cell and then insert the current date in the format "dd/mm" into that cell.

  5. User

    When I run the code, 'Target.Offset(0, 3).Value = Target.Offset(0, 9).Value' enters a value into column G on the same row as the Target. I want the code to use the Target Row and then determine the first empty cell in the same row of column H, I, J, K, L and then insert the current date in the format dd/mm

  6. Assistant

    Apologies for the confusion. To find the first empty cell in columns H to L on the same row as the target, you can loop through each column until an empty cell is found. Here's the updated code to achieve that:

    Private Sub PSJulAug(ByVal Target As Range)
        If Target.Offset(0, -2).Value = "-" Then
            MsgBox "Service Plan email has already been sent"
        ElseIf Target.Offset(0, -2).Value <> "-" Then
            Dim currentDate As String
            Target.Offset(0, 3).Value = Target.Offset(0, 9).Value
            
            ' Find the first empty cell in columns H to L on the same row
            Dim columnRange As Range
            Dim emptyCell As Range
            
            For Each columnRange In Range("H" & Target.Row & ":L" & Target.Row)
                If columnRange.Value = "" Then
                    Set emptyCell = columnRange
                    Exit For
                End If
            Next columnRange
            
            ' Insert the current date in the first empty cell
            If Not emptyCell Is Nothing Then
                currentDate = Format(Date, "dd/mm")
                emptyCell.Value = currentDate
            End If
            
            Call OpenPSJulAug
        End If
        doubleClickFlag = False
    End Sub
    

    This revised code will loop through columns H to L on the same row as the target, and it will find the first empty cell in that range. If an empty cell is found, it will insert the current date in the format "dd/mm" into that cell.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.