| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
elseif
If Target.Column = 12 And IsNumeric(Target.Value) Then
If Target.value <= 0 Then Call Copycomp(Target) Target.Value = 0 elseIf Target.Column = 12 and Target.Value = 10 Then Target.Value = 10 End If end if -- Regards, Tom Ogilvy "Curt" wrote: useing code that follows have tried to have code call copycomp sometimes will also code changes all entries to 0 not sure where to go stumped. Think there should be a way to use elseif. Tried to no avail. Want column to be as code calls for. Have 3 calls befor this one that work fine. Thanks If Target.Column = 12 And Target.Value <= 0 And IsNumeric(Target.Value) Then _ Call Copycomp(Target) 'do this then'_ If Target.Column = (12) <= 0 Then _ Target.Value = 0 If Target.Column = (12) = 10 Then _ Target.Value = 10 ' End If ' If Target.Column = (12) And Target.Value < 10 Then ' Target.Value = 10 ' If Target.Column = 12 And Target.Value <= 0 Then _ ' Target.Value = 0 Application.EnableEvents = True ' End If |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
elseif
Dumb me eh? Know better next time.
Thanks "Tom Ogilvy" wrote: Application.EnableEvents = False If Target.Column = 12 And IsNumeric(Target.Value) Then If Target.value <= 0 Then Call Copycomp(Target) Target.Value = 0 elseIf Target.Column = 12 and Target.Value = 10 Then Target.Value = 10 End If end if Application.EnableEvents = True because you are changing the value of the cell that triggered the initial event, you get recursive calls to that event. So you need to disable events as you have in most of your other code. -- Regards, Tom Ogilvy "Curt" wrote: For some reason copycomp now copys 200 or more rows. here is copycomp something must be missing. Can you see why it would copy so many rows after this change which works on sheet 1 Have same code in other events in sheet ! when I changed rngpaste offset to (1,0) it copied every other row Thanks Public Sub Copycomp(ByVal Target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Comp") Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(0, 0) Application.EnableEvents = False Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(Target.Offset(0, -7), Target.Offset(0, 0)) Range(Target.Offset(0, -7), Target.Offset(0, 0)).Copy _ Destination:=rngPaste rngPaste.Offset(0, 7) = Target Application.EnableEvents = True End Sub "Tom Ogilvy" wrote: If Target.Column = 12 And IsNumeric(Target.Value) Then If Target.value <= 0 Then Call Copycomp(Target) Target.Value = 0 elseIf Target.Column = 12 and Target.Value = 10 Then Target.Value = 10 End If end if -- Regards, Tom Ogilvy "Curt" wrote: useing code that follows have tried to have code call copycomp sometimes will also code changes all entries to 0 not sure where to go stumped. Think there should be a way to use elseif. Tried to no avail. Want column to be as code calls for. Have 3 calls befor this one that work fine. Thanks If Target.Column = 12 And Target.Value <= 0 And IsNumeric(Target.Value) Then _ Call Copycomp(Target) 'do this then'_ If Target.Column = (12) <= 0 Then _ Target.Value = 0 If Target.Column = (12) = 10 Then _ Target.Value = 10 ' End If ' If Target.Column = (12) And Target.Value < 10 Then ' Target.Value = 10 ' If Target.Column = 12 And Target.Value <= 0 Then _ ' Target.Value = 0 Application.EnableEvents = True ' End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
elseif
Application.EnableEvents = False
If Target.Column = 12 And IsNumeric(Target.Value) Then If Target.value <= 0 Then Call Copycomp(Target) Target.Value = 0 elseIf Target.Column = 12 and Target.Value = 10 Then Target.Value = 10 End If end if Application.EnableEvents = True because you are changing the value of the cell that triggered the initial event, you get recursive calls to that event. So you need to disable events as you have in most of your other code. -- Regards, Tom Ogilvy "Curt" wrote: For some reason copycomp now copys 200 or more rows. here is copycomp something must be missing. Can you see why it would copy so many rows after this change which works on sheet 1 Have same code in other events in sheet ! when I changed rngpaste offset to (1,0) it copied every other row Thanks Public Sub Copycomp(ByVal Target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Comp") Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(0, 0) Application.EnableEvents = False Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(Target.Offset(0, -7), Target.Offset(0, 0)) Range(Target.Offset(0, -7), Target.Offset(0, 0)).Copy _ Destination:=rngPaste rngPaste.Offset(0, 7) = Target Application.EnableEvents = True End Sub "Tom Ogilvy" wrote: If Target.Column = 12 And IsNumeric(Target.Value) Then If Target.value <= 0 Then Call Copycomp(Target) Target.Value = 0 elseIf Target.Column = 12 and Target.Value = 10 Then Target.Value = 10 End If end if -- Regards, Tom Ogilvy "Curt" wrote: useing code that follows have tried to have code call copycomp sometimes will also code changes all entries to 0 not sure where to go stumped. Think there should be a way to use elseif. Tried to no avail. Want column to be as code calls for. Have 3 calls befor this one that work fine. Thanks If Target.Column = 12 And Target.Value <= 0 And IsNumeric(Target.Value) Then _ Call Copycomp(Target) 'do this then'_ If Target.Column = (12) <= 0 Then _ Target.Value = 0 If Target.Column = (12) = 10 Then _ Target.Value = 10 ' End If ' If Target.Column = (12) And Target.Value < 10 Then ' Target.Value = 10 ' If Target.Column = 12 And Target.Value <= 0 Then _ ' Target.Value = 0 Application.EnableEvents = True ' End If |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
elseif
For some reason copycomp now copys 200 or more rows. here is copycomp
something must be missing. Can you see why it would copy so many rows after this change which works on sheet 1 Have same code in other events in sheet ! when I changed rngpaste offset to (1,0) it copied every other row Thanks Public Sub Copycomp(ByVal Target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Comp") Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(0, 0) Application.EnableEvents = False Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(Target.Offset(0, -7), Target.Offset(0, 0)) Range(Target.Offset(0, -7), Target.Offset(0, 0)).Copy _ Destination:=rngPaste rngPaste.Offset(0, 7) = Target Application.EnableEvents = True End Sub "Tom Ogilvy" wrote: If Target.Column = 12 And IsNumeric(Target.Value) Then If Target.value <= 0 Then Call Copycomp(Target) Target.Value = 0 elseIf Target.Column = 12 and Target.Value = 10 Then Target.Value = 10 End If end if -- Regards, Tom Ogilvy "Curt" wrote: useing code that follows have tried to have code call copycomp sometimes will also code changes all entries to 0 not sure where to go stumped. Think there should be a way to use elseif. Tried to no avail. Want column to be as code calls for. Have 3 calls befor this one that work fine. Thanks If Target.Column = 12 And Target.Value <= 0 And IsNumeric(Target.Value) Then _ Call Copycomp(Target) 'do this then'_ If Target.Column = (12) <= 0 Then _ Target.Value = 0 If Target.Column = (12) = 10 Then _ Target.Value = 10 ' End If ' If Target.Column = (12) And Target.Value < 10 Then ' Target.Value = 10 ' If Target.Column = 12 And Target.Value <= 0 Then _ ' Target.Value = 0 Application.EnableEvents = True ' End If |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Elseif? | Excel Worksheet Functions | |||
| IF..Then..ELSE.. ELSEIF | Excel Programming | |||
| If, ElseIf | Excel Programming | |||
| ElseIf | Excel Programming | |||
| If...Elseif...End If | Excel Programming | |||
Linear Mode
