Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet change Macro part 2
I added some code to a macro Bob Phillips created, to copy in the orginal
data if one of the 32 cells were 0. The trouble I'm having is that if hit the arrow key instead of return. The wrong column gets copied over. Any ideas on how to get it to select the right cell each time? I'm sure it has to do with my 'With target ' stmt, but I can't get it select the right cell ever if I do that. Thanks Vick Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "L32,H32,F32" '<== change to suit Dim cell As Range On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target For Each cell In Me.Range(Target.Offset(1, 0), Target.End(xlDown)) If IsNumeric(cell.Value) Then cell.Value = cell.Value * (1 + .Value) End If Next cell End With End If If Intersect(Target, Me.Range(WS_RANGE)) = 0 Then With Target ActiveCell.Offset(-29, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy ActiveCell.Offset(30, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End With End If ws_exit: Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet change Macro Question | Excel Discussion (Misc queries) | |||
Macro to change part of cell | Excel Discussion (Misc queries) | |||
copy part of a worksheet into a worksheet in the same file/keepi. | Excel Worksheet Functions | |||
Running of Worksheet Change Macro breaks undo functionality. | Excel Discussion (Misc queries) | |||
Using this Automatic Resizing Macro with Worksheet Change | Excel Discussion (Misc queries) |