Code to trap data entry outside of range
 

Hi,

I found this code from Bob Phillips that evaluates a cell entry
against the previous cells entry in the column and generates a
warning. Can anybody help me modify this code to evaluate an average
of the previous 10 rows (i.e. a range)?

Thanks.

Alan

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
Application.EnableEvents = False
On Error GoTo sub_exit
With Target
If (.Column 2 And .Column < 9) Then
If .Row = 3 Or .Row = 8 Or .Row = 13 Or .Row = 14 Then
If .Value .Offset(0, -1) * 1.2 Then ' 20% greater
ans = MsgBox("Value of " & .Value & " is more than
20%
greater " & _
"than the " & .Offset(0, -1).Value &
" for
" & Format(.Offset(-.Row + 1, -1), "dd mmm yyyy") & vbCrLf & _
vbCrLf & _
"Are you sure? Please recheck entry!"
&
vbCrLf & _
"(hit 'OK' to re-input, 'Cancel' to
accept
and ignore limit)", _
vbOKCancel, _
"Vinay's Value Checker")
If ans = 2 Then
.Offset(1, 0).Activate
End If
End If
End If
End If
End With
sub_exit:
Application.EnableEvents = True
End Sub

Code to trap data entry outside of range
 

use something like this

If .Value WorksheetFunction.Average(Range(.Offset(0, -1),
..Offset(0, -10))) Then

You will have to assure that Offset(0, -1) and .Offset(0, -10) are not off
the sheet, possibly by ignoring Targets too close to the top of the sheet
using, perhaps using something like,

If Intersect(Target, Range(.Rows(1),.Rows(10))) Then Exit Sub


"Alan Campbell" wrote in message
om...
Hi,

I found this code from Bob Phillips that evaluates a cell entry
against the previous cells entry in the column and generates a
warning. Can anybody help me modify this code to evaluate an average
of the previous 10 rows (i.e. a range)?

Thanks.

Alan

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
Application.EnableEvents = False
On Error GoTo sub_exit
With Target
If (.Column 2 And .Column < 9) Then
If .Row = 3 Or .Row = 8 Or .Row = 13 Or .Row = 14 Then
If .Value .Offset(0, -1) * 1.2 Then ' 20% greater
ans = MsgBox("Value of " & .Value & " is more than
20%
greater " & _
"than the " & .Offset(0, -1).Value &
" for
" & Format(.Offset(-.Row + 1, -1), "dd mmm yyyy") & vbCrLf & _
vbCrLf & _
"Are you sure? Please recheck entry!"
&
vbCrLf & _
"(hit 'OK' to re-input, 'Cancel' to
accept
and ignore limit)", _
vbOKCancel, _
"Vinay's Value Checker")
If ans = 2 Then
.Offset(1, 0).Activate
End If
End If
End If
End If
End With
sub_exit:
Application.EnableEvents = True
End Sub

Code to trap data entry outside of range
 

Rows, not Columns: Range(.Offset(-1, 0), .Offset(-10, 0))

"Bob Kilmer" wrote in message
...
use something like this

If .Value WorksheetFunction.Average(Range(.Offset(0, -1),
.Offset(0, -10))) Then

You will have to assure that Offset(0, -1) and .Offset(0, -10) are not
off
the sheet, possibly by ignoring Targets too close to the top of the sheet
using, perhaps using something like,

If Intersect(Target, Range(.Rows(1),.Rows(10))) Then Exit Sub


"Alan Campbell" wrote in message
om...
Hi,

I found this code from Bob Phillips that evaluates a cell entry
against the previous cells entry in the column and generates a
warning. Can anybody help me modify this code to evaluate an average
of the previous 10 rows (i.e. a range)?

Thanks.

Alan

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
Application.EnableEvents = False
On Error GoTo sub_exit
With Target
If (.Column 2 And .Column < 9) Then
If .Row = 3 Or .Row = 8 Or .Row = 13 Or .Row = 14 Then
If .Value .Offset(0, -1) * 1.2 Then ' 20% greater
ans = MsgBox("Value of " & .Value & " is more than
20%
greater " & _
"than the " & .Offset(0, -1).Value &
" for
" & Format(.Offset(-.Row + 1, -1), "dd mmm yyyy") & vbCrLf & _
vbCrLf & _
"Are you sure? Please recheck entry!"
&
vbCrLf & _
"(hit 'OK' to re-input, 'Cancel' to
accept
and ignore limit)", _
vbOKCancel, _
"Vinay's Value Checker")
If ans = 2 Then
.Offset(1, 0).Activate
End If
End If
End If
End If
End With
sub_exit:
Application.EnableEvents = True
End Sub

Code to trap data entry outside of range
 

Bob,

Thanks. I will give try your coding changes and let you know how is goes.

Alan

"Bob Kilmer" wrote in message ...
Rows, not Columns: Range(.Offset(-1, 0), .Offset(-10, 0))

"Bob Kilmer" wrote in message
...
use something like this

If .Value WorksheetFunction.Average(Range(.Offset(0, -1),
.Offset(0, -10))) Then

You will have to assure that Offset(0, -1) and .Offset(0, -10) are not
off
the sheet, possibly by ignoring Targets too close to the top of the sheet
using, perhaps using something like,

If Intersect(Target, Range(.Rows(1),.Rows(10))) Then Exit Sub


"Alan Campbell" wrote in message
om...
Hi,

I found this code from Bob Phillips that evaluates a cell entry
against the previous cells entry in the column and generates a
warning. Can anybody help me modify this code to evaluate an average
of the previous 10 rows (i.e. a range)?

Thanks.

Alan

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans
Application.EnableEvents = False
On Error GoTo sub_exit
With Target
If (.Column 2 And .Column < 9) Then
If .Row = 3 Or .Row = 8 Or .Row = 13 Or .Row = 14 Then
If .Value .Offset(0, -1) * 1.2 Then ' 20% greater
ans = MsgBox("Value of " & .Value & " is more than
20%
greater " & _
"than the " & .Offset(0, -1).Value &
" for
" & Format(.Offset(-.Row + 1, -1), "dd mmm yyyy") & vbCrLf & _
vbCrLf & _
"Are you sure? Please recheck entry!"
&
vbCrLf & _
"(hit 'OK' to re-input, 'Cancel' to
accept
and ignore limit)", _
vbOKCancel, _
"Vinay's Value Checker")
If ans = 2 Then
.Offset(1, 0).Activate
End If
End If
End If
End If
End With
sub_exit:
Application.EnableEvents = True
End Sub