VBA - Method 'Range of Object ' Worksheet Failed

[chuffles]

Hi all, new to VBA and likely writing very bad code...

Attempting to write macro where upon typing "auto" in column KI, sets KJ = -KZ
However KZ uses KJ in its own formula, so would cause a circular reference. Workaround is as follows:

1. User types "auto" into col KI
2. KZ formula becomes = KZ.Value + n("KZ.Formula")
3. KJ = - KZ
4. KJ gets paste-valued
5. KZ formula is reverted back to just KZ.Formula

However I'm running into an issue in this line below, causing the 1004 Error. Any help is appreciated

 NewFormula = "=" & Wks.Range(Target.Offset(0, 16)).Value & "+N(""" & Wks.Range(Target.Offset(0, 16)).Formula & """)"

Private Sub Worksheet_Change(ByVal Target As Range)


Dim NewFormula As String
Dim Wks As Worksheet

Set Wks = Worksheets("Input")



        If Not Intersect(Range("KI1:KI2000"), Target) Is Nothing Then

        If Target.Rows.Count > 1 Then Exit Sub

        If Target.Value = "auto" Then
        Stop
        
            On Error Resume Next
            Application.EnableEvents = False
        
        
           
            
            NewFormula = "=" & Wks.Range(Target.Offset(0, 16)).Value & "+N(""" & Wks.Range(Target.Offset(0, 16)).Formula & """)"
            Range(Target.Offset(0, 16)).Value = "=-KZ"
            
            
            Range(Target.Offset(0, 1)).Copy
            Range(Target.Offset(0, 1)).PasteSpecial Paste:=xlPasteValues
            
            OldFormula = Range(Target.Offset(0, 16)).Formula
            WhereFound = InStr(1, OldFormula, "+N(")
            
            StartAt = WhereFound + 4
            EndAt = Len(OldFormula) - 2
            LengthToGet = EndAt - StartAt + 1
            FinalFormula = Mid(OldFormula, StartAt, LengthToGet)
            Range(Target.Offset(0, 16)).Formula = NewFormula
            
            Application.EnableEvents = True

        End If

        End If
End Sub

 

[Zot]

I'm confused trying to understand your code compare to your description

Anyway, instead of writing Range(Target.Offset(0, 1)), you should just use Target.Offset(0, 1) because Target itself is already a range. I think this cause the error.

You mentioned about KZ formula becomes = KZ.Value + n("KZ.Formula") but Target.Offset(0, 16) is not column KZ but KY