Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Contents of Offset cells automatically
Hi all, I got macro below which automatically put figures in Offset
cells when I put any figure in Target cell. It works fine but I want that when I Clear Contents of Target cell by pressing Delete button on Keyboard then Contents of Offset cells should also be cleared automatically. Please can any friend can help Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoops Application.EnableEvents = False If Not Intersect(Target, Range("A2:A5")) Is Nothing Then Target.Offset(, 1) = Target * 52 Target.Offset(, 2) = Target * 12 Else End If Whoops: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Contents of Offset cells automatically
Hi,
Try this Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoops Application.EnableEvents = False If Not Intersect(Target, Range("A2:A5")) Is Nothing Then If Len(Target.Value) 0 Then Target.Offset(, 1) = Target * 52 Target.Offset(, 2) = Target * 12 Else Target.Offset(, 1).ClearContents Target.Offset(, 2).ClearContents End If End If Whoops: Application.EnableEvents = True End Sub Mike "K" wrote: Hi all, I got macro below which automatically put figures in Offset cells when I put any figure in Target cell. It works fine but I want that when I Clear Contents of Target cell by pressing Delete button on Keyboard then Contents of Offset cells should also be cleared automatically. Please can any friend can help Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoops Application.EnableEvents = False If Not Intersect(Target, Range("A2:A5")) Is Nothing Then Target.Offset(, 1) = Target * 52 Target.Offset(, 2) = Target * 12 Else End If Whoops: Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Contents of Offset cells automatically
Hi
Try this: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoops If Not Intersect(Target, Range("A2:A5")) Is Nothing Then Application.EnableEvents = False If Target.Value < "" Then Target.Offset(, 1) = Target * 52 Target.Offset(, 2) = Target * 12 Else Target.Offset(, 1) = "" Target.Offset(, 2) = "" End If End If Whoops: Application.EnableEvents = True End Sub Regards, Per "K" skrev i meddelelsen ... Hi all, I got macro below which automatically put figures in Offset cells when I put any figure in Target cell. It works fine but I want that when I Clear Contents of Target cell by pressing Delete button on Keyboard then Contents of Offset cells should also be cleared automatically. Please can any friend can help Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoops Application.EnableEvents = False If Not Intersect(Target, Range("A2:A5")) Is Nothing Then Target.Offset(, 1) = Target * 52 Target.Offset(, 2) = Target * 12 Else End If Whoops: Application.EnableEvents = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Contents of Offset cells automatically
thanks lot guys
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clear contents of cells if a condition is met | Excel Worksheet Functions | |||
Clear Contents Of Cells Where Value = 0 | Excel Worksheet Functions | |||
Paste, clear and ignore contents and... automatically! | Excel Programming | |||
clear cells unless contents are in bold | Excel Programming | |||
Clear Contents - NonBold cells | Excel Discussion (Misc queries) |