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

On the same row of my active sheet, if column G has a value and column I has a date then…

  1. User

    On the same row of my active sheet, if column G has a value and column I has a date then if I insert a date in column H of the same row then check If column G does not have a value, then pop up the message 'Service Schedule period not present" and Exit Sub check If column I does not have a value, then pop up the message 'Date Service completed not present" and Exit Sub If column G and I both have values then change the value of column B on the same row to the value 'Serviced'. Then find the next available empty row in the range B:J and change the cell values of the new row as described; Column B value = 'Service' Column C value = Date Value displayed in column H from the row where column B was recently changed to 'Serviced' Column G value = Value dispalyed in column G from the row where column B was recently changed to 'Serviced' Column H value = Value dispalyed in column H from the row where column B was recently changed to 'Serviced' Column J value = Value dispalyed in column J from the row where column B was recently changed to 'Serviced' Column L value = Value dispalyed in column L from the row where column B was recently changed to 'Serviced' Wait 1 second then pop up message "Serviced Due Date will be deleted and added to New Service row" Then in Column H of the same row where column B was recently changed to 'Serviced' change the value to ""

  2. Assistant

    Here is the code you can use to achieve the desired functionalities:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim nextRow As Long
        
        Set ws = ActiveSheet
        
        'check if column G has a value and column I has a date
        If Not Intersect(Target, ws.Range("G:G, I:I")) Is Nothing Then
            If ws.Cells(Target.Row, "G").Value <> "" And IsDate(ws.Cells(Target.Row, "I").Value) Then
                'check if column H is populated
                If ws.Cells(Target.Row, "H").Value = "" Then
                    'check if column G does not have a value
                    If ws.Cells(Target.Row, "G").Value = "" Then
                        MsgBox "Service Schedule period not present"
                        Exit Sub
                    End If
                    
                    'check if column I does not have a value
                    If Not IsDate(ws.Cells(Target.Row, "I").Value) Then
                        MsgBox "Date Service completed not present"
                        Exit Sub
                    End If
                    
                    'change the value of column B to 'Serviced'
                    ws.Cells(Target.Row, "B").Value = "Serviced"
                    
                    'find the next available empty row in range B:J
                    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
                    nextRow = lastRow + 1
                    
                    'copy cell values to new row
                    ws.Cells(nextRow, "B").Value = "Service"
                    ws.Cells(nextRow, "C").Value = ws.Cells(Target.Row, "H").Value
                    ws.Cells(nextRow, "G").Value = ws.Cells(Target.Row, "G").Value
                    ws.Cells(nextRow, "H").Value = ws.Cells(Target.Row, "H").Value
                    ws.Cells(nextRow, "J").Value = ws.Cells(Target.Row, "J").Value
                    ws.Cells(nextRow, "L").Value = ws.Cells(Target.Row, "L").Value
                    
                    'wait 1 second
                    Application.Wait (Now + TimeValue("0:00:01"))
                    
                    'pop up message
                    MsgBox "Serviced Due Date will be deleted and added to New Service row"
                    
                    'change the value of column H to ""
                    ws.Cells(Target.Row, "H").Value = ""
                End If
            End If
        End If
    End Sub
    

    Note: This code should be placed in the Sheet module of the worksheet where you want it to run. To do this, right-click on the sheet tab, select "View Code," and paste the code into the code window that opens.

    This code utilizes the Worksheet_Change event, which triggers whenever there are changes in the worksheet. It checks if the change happens in column G or I, and if both columns have values and meets the specified conditions, it performs the necessary operations. The code also includes error handling to display pop-up messages if certain conditions are not met.

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