Object required error on Worksheet_Change

[Jockster]

The following code is supposed to alert the user if the number they have entered is non-sequential.
When run though, at the Set oldVal line, it errors and I can't figure out what is wrong.

Sub Worksheet_Change(ByVal Target As Range)
Dim oldVal As Range, newVal As Range
Set Target = Range("A" & Rows.Count).End(xlUp)
With Target
If Target.Value > 1 Then
Set oldVal = Target.Offset(-1, 0).Value
Set newVal = Target.Value
 
 If newVal <> oldVal + 1 Then
 MsgBox "This is not a sequential number", , "Invalid Number?"
 End If
'End If
 End With
 
End Sub

Any help will be appreciated.
Regards.

 

[Jonmo1]

You've dimmed oldVal and newVal as Ranges, but you're trying to assign them as values...

Change these

Set oldVal = Target.Offset(-1, 0).Value
Set newVal = Target.Value

to

Set oldVal = Target.Offset(-1, 0)
Set newVal = Target

 

[Sektor]

...
Set oldVal = Target.Offset(-1, 0)
Set newVal = Target
...

 

[kpark91]

Oops.

 

[JamesW]

Hi there,

You are using Set when you have no object to assign; oldVal and newVal are not Ranges, they are numerical values:

Sub Worksheet_Change(ByVal Target As Range)
    Dim oldVal As Long, newval As Long
    Set Target = Range("A" & Rows.Count).End(xlUp)
    With Target
        If Target.Value > 1 Then
            oldVal = Target.Offset(-1, 0).Value
            newval = Target.Value
            If newval <> oldVal + 1 Then
                MsgBox "This is not a sequential number", , "Invalid Number?"
            End If
        End If
    End With
End Sub

 

[Jonmo1]

Also, I would recommend NOT resetting the Target Object Variable..

It becomes confusing if you're trying to refer to the cell that got changed, or the last occupied cell in column A.
They are not necessarliy (most likely not) the same cell

 

[JackDanIce]

As per other replies, you've mixed up your variable types and what you are assigning to them. The code works as:

Sub Worksheet_Change(ByVal Target As Range)
 
Dim oldVal As Long, newVal As Long
 
Set Target = Range("A" & Rows.Count).End(xlUp)
With Target
    If .Value > 1 Then
        oldVal = .Offset(-1, 0).Value
        newVal = .Value
    End If
    If newVal <> oldVal + 1 Then MsgBox "This is not a sequential number", , "Invalid Number?"
End With
 
End Sub

 

[JackDanIce]

Your macro (well modified code I suggested based on yours), will also fail if you enter any value that is not a positive integer

 

[Jockster]

Excellent.
Thanks to all who responded. Sorted now.