VBA Code to clearcontents

Afternoon Guys,

I'm try to make these codes work the first code is the following,

I input a value in row B once inputed it moves to Row A then should clear from Row B its does couple of times.

CODE
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myval As Variant
On Error Resume Next
If Application.Intersect(Target, Range("B1:B5")) _
Is Nothing Then
Exit Sub
Else
myval = Target.Value
Target.Offset(0, -1).Value = _
Target.Offset(0, -1).Value + myval
End If
On Error GoTo 0
Sheet1.Range("B1:B5").Select
Selection.ClearContents
Sheet1.Range("B1:B3000").Select
End Sub

My next issue is

i want to run the same code but with

myval = Target.Value
Target.Offset(0, -1).Value = _
Target.Offset(0, -1).Value - myval
End If

but in row G

Your help will be must appreciated

Regards
Paul

try this

Please Login or Register  to view this content.

try this

Please Login or Register  to view this content.

hi this code keeps coming back with a error object range fail

the first part of this code works fine its the second where my problem is as i'm trying subtract the value

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myval As Variant
On Error Resume Next
If Application.Intersect(Target, Range("B1:B3000")) _
Is Nothing Then
Exit Sub
Else
myval = Target.Value
Target.Offset(0, -1).Value = _
Target.Offset(0, -1).Value + myval
End If
With ActiveSheet
Intersect(.UsedRange, .Columns("B")).ClearContents

End With
End Sub
Private Sub Worksheet_Change1(ByVal Target As Range)
Dim myval As Variant
On Error Resume Next
If Application.Intersect(Target, Range("G1:G5")) _
Is Nothing Then
Exit Sub
Else
myval = Target.Value
Target.Offset(0, -1).Value = _
Target.Offset(0, -1).Value - myval
End If
With ActiveSheet
Intersect(.UsedRange, .Columns("G")).ClearContents
End Sub

the first part of this code works fine its the second where my problem is as i'm trying subtract the value

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myval As Variant
On Error Resume Next
If Application.Intersect(Target, Range("B1:B3000")) _
Is Nothing Then
Exit Sub
Else
myval = Target.Value
Target.Offset(0, -1).Value = _
Target.Offset(0, -1).Value + myval
End If
With ActiveSheet
Intersect(.UsedRange, .Columns("B")).ClearContents

End With
End Sub
Private Sub Worksheet_Change1(ByVal Target As Range)
Dim myval As Variant
On Error Resume Next
If Application.Intersect(Target, Range("G1:G5")) _
Is Nothing Then
Exit Sub
Else
myval = Target.Value
Target.Offset(0, -1).Value = _
Target.Offset(0, -1).Value - myval
End If
With ActiveSheet
Intersect(.UsedRange, .Columns("G")).ClearContents
End Sub