ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Error in Code (https://www.excelbanter.com/excel-programming/329008-error-code.html)

Jim May
Error in Code
 
With:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
Target.Value = Target.Value * -1
End If
Application.EnableEvents = True
End Sub

When I select from Col C either Sale of Item or Shrinkage and enter say 5 in
my Col F my intention (with the code above is to convert the 5 in Cell F12
(say) to -5.
But that is not happening.
Can someone assist me?
TIA,

Bob Phillips[_7_]
Error in Code
 
Jim,

This works fine for me

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing Then
If Target.Offset(0, -3).Value = "Sale of Item" Or _
Target.Offset(0, -3).Value = "Shrinkage" Then
Target.Value = Target.Value * -1
End If
End If
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

"Jim May" wrote in message
news:5UHfe.2801$It1.87@lakeread02...
With:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
Target.Value = Target.Value * -1
End If
Application.EnableEvents = True
End Sub

When I select from Col C either Sale of Item or Shrinkage and enter say 5
in
my Col F my intention (with the code above is to convert the 5 in Cell F12
(say) to -5.
But that is not happening.
Can someone assist me?
TIA,

Tom Ogilvy
Error in Code
 
There is nothing specifically wrong with your code. Assuming you have this
code in the sheet module, I would do this:
Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Count 1 then exit sub
On Error goto ErrHandler
Application.EnableEvents = False

If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
if isnumeric(Target) then
Target.Value = abs(Target.Value) * -1
end if
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Then run code like this

Sub EventsBackOn()
Applicationl.EnableEvents = True
End Sub

then see if it works. If not, then the next step is to make sure you have
an exact match with column C values.

--
Regards,
Tom Ogilvy


"Jim May" wrote in message
news:5UHfe.2801$It1.87@lakeread02...
With:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
Target.Value = Target.Value * -1
End If
Application.EnableEvents = True
End Sub

When I select from Col C either Sale of Item or Shrinkage and enter say 5
in
my Col F my intention (with the code above is to convert the 5 in Cell F12
(say) to -5.
But that is not happening.
Can someone assist me?
TIA,

Tom Ogilvy
Error in Code
 


Of course you need a line continuation after the OR (I used your original
code and forgot to add it).

Just to be cleaner, you might limit the handler to columns farther to the
right than column 3



Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Count 1 then exit sub
if Target.Column < 4 then exit sub
On Error goto ErrHandler
Application.EnableEvents = False

If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or _
Target.Offset(0, -3).Value = "Shrinkage" Then _
if isnumeric(Target) then
Target.Value = abs(Target.Value) * -1
end if
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
There is nothing specifically wrong with your code. Assuming you have
this
code in the sheet module, I would do this:
Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Count 1 then exit sub
On Error goto ErrHandler
Application.EnableEvents = False

If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
if isnumeric(Target) then
Target.Value = abs(Target.Value) * -1
end if
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Then run code like this

Sub EventsBackOn()
Applicationl.EnableEvents = True
End Sub

then see if it works. If not, then the next step is to make sure you have
an exact match with column C values.

--
Regards,
Tom Ogilvy


"Jim May" wrote in message
news:5UHfe.2801$It1.87@lakeread02...
With:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
Target.Value = Target.Value * -1
End If
Application.EnableEvents = True
End Sub

When I select from Col C either Sale of Item or Shrinkage and enter say
5
in
my Col F my intention (with the code above is to convert the 5 in Cell
F12
(say) to -5.
But that is not happening.
Can someone assist me?
TIA,

Jim May
Error in Code
 
Bob;
Thanks, missed the use of the double-If statement.
Appreciate your help.
Jim

"Bob Phillips" wrote in message
...
Jim,

This works fine for me

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing
Then
If Target.Offset(0, -3).Value = "Sale of Item" Or _
Target.Offset(0, -3).Value = "Shrinkage" Then
Target.Value = Target.Value * -1
End If
End If
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

"Jim May" wrote in message
news:5UHfe.2801$It1.87@lakeread02...
With:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
Target.Value = Target.Value * -1
End If
Application.EnableEvents = True
End Sub

When I select from Col C either Sale of Item or Shrinkage and enter say
5
in
my Col F my intention (with the code above is to convert the 5 in Cell
F12
(say) to -5.
But that is not happening.
Can someone assist me?
TIA,

Jim May
Error in Code
 
Thanks Tom, have made a copy to take with me.
Jim

"Tom Ogilvy" wrote in message
...
There is nothing specifically wrong with your code. Assuming you have
this
code in the sheet module, I would do this:
Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Count 1 then exit sub
On Error goto ErrHandler
Application.EnableEvents = False

If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
if isnumeric(Target) then
Target.Value = abs(Target.Value) * -1
end if
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Then run code like this

Sub EventsBackOn()
Applicationl.EnableEvents = True
End Sub

then see if it works. If not, then the next step is to make sure you have
an exact match with column C values.

--
Regards,
Tom Ogilvy


"Jim May" wrote in message
news:5UHfe.2801$It1.87@lakeread02...
With:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
Target.Value = Target.Value * -1
End If
Application.EnableEvents = True
End Sub

When I select from Col C either Sale of Item or Shrinkage and enter say
5
in
my Col F my intention (with the code above is to convert the 5 in Cell
F12
(say) to -5.
But that is not happening.
Can someone assist me?
TIA,

Jim May
Error in Code
 
Got the amended code in time to take it also...
Jim

"Jim May" wrote in message
news:JPIfe.2803$It1.116@lakeread02...
Thanks Tom, have made a copy to take with me.
Jim

"Tom Ogilvy" wrote in message
...
There is nothing specifically wrong with your code. Assuming you have
this
code in the sheet module, I would do this:
Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Count 1 then exit sub
On Error goto ErrHandler
Application.EnableEvents = False

If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
if isnumeric(Target) then
Target.Value = abs(Target.Value) * -1
end if
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Then run code like this

Sub EventsBackOn()
Applicationl.EnableEvents = True
End Sub

then see if it works. If not, then the next step is to make sure you
have
an exact match with column C values.

--
Regards,
Tom Ogilvy


"Jim May" wrote in message
news:5UHfe.2801$It1.87@lakeread02...
With:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing
_
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
Target.Value = Target.Value * -1
End If
Application.EnableEvents = True
End Sub

When I select from Col C either Sale of Item or Shrinkage and enter
say
5
in
my Col F my intention (with the code above is to convert the 5 in Cell
F12
(say) to -5.
But that is not happening.
Can someone assist me?
TIA,


All times are GMT +1. The time now is 05:25 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com