Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error in this code | Excel Worksheet Functions | |||
Getting error in code | Excel Programming | |||
Error in my code? | Excel Programming | |||
How can I still go to the error-code after a On Error Goto? | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |