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

I moved the code below from the Sheet VBA into a Module. Now I am getting an error on…

  1. User

    I moved the code below from the Sheet VBA into a Module. Now I am getting an error on the line 'Target.Offset(0, -5).Value = "Serviced"' : Public Sub ServiceCreation() MsgBox "A New Service row will now be created" Target.Offset(0, -5).Value = "Serviced" Target.Offset(0, 1).Value = "" Dim newRow As Long newRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1 ActiveSheet.Range("B" & newRow).Value = "Service" ActiveSheet.Range("C" & newRow).Value = Target.Offset(0, 2).Value ActiveSheet.Range("F" & newRow).Value = Target.Offset(0, -1).Value ActiveSheet.Range("J" & newRow).Value = Target.Offset(0, 3).Value ActiveSheet.Range("L" & newRow).Value = Target.Offset(0, 5).Value ActiveSheet.Range("H" & newRow).Value = Target.Offset(0, 2).Value + Target.Value End Sub

  2. Assistant

    The error you are encountering is likely due to the fact that the Target variable is not defined within the ServiceCreation() subroutine. In order to use the Target object, you need to either pass it as an argument to the subroutine or redefine it within the subroutine.

    Here's an example of how you can modify your code to properly define and use the Target variable:

    Public Sub ServiceCreation()
        MsgBox "A New Service row will now be created"
        Dim Target As Range ' Define the Target variable
        
        ' Set the Target variable to the active cell
        ' This assumes that the active cell is the cell where the code is triggered
        Set Target = ActiveCell
    
        Target.Offset(0, -5).Value = "Serviced"
        Target.Offset(0, 1).Value = ""
        Dim newRow As Long
        newRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
        ActiveSheet.Range("B" & newRow).Value = "Service"
        ActiveSheet.Range("C" & newRow).Value = Target.Offset(0, 2).Value
        ActiveSheet.Range("F" & newRow).Value = Target.Offset(0, -1).Value
        ActiveSheet.Range("J" & newRow).Value = Target.Offset(0, 3).Value
        ActiveSheet.Range("L" & newRow).Value = Target.Offset(0, 5).Value
        ActiveSheet.Range("H" & newRow).Value = Target.Offset(0, 2).Value + Target.Value
    End Sub
    

    Make sure to adjust the code according to your specific needs and ensure that the Target variable is properly defined and set before accessing its properties.

    Additionally, make sure that the active cell is in the expected location when running this subroutine, as the code relies on relative offsets from the active cell.