Please write me a VBA code to do the following; When I double click on a cell in column…
- 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
- 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 SubHere's how to add this code in Excel:
- Press
Alt + F11to open the VBA editor. - In the Project Explorer on the left side, find the worksheet where you want this code to work (e.g., Sheet1).
- Double-click on the worksheet name to open the code window.
- Paste the code into the code window.
- 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.
- Press
- 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 = currentDateCall OpenPSJulAug End If doubleClickFlag = FalseEnd Sub
- 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 SubThis 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.
- 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
- 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 SubThis 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.