V
Vick
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
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