Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Corresponding Cell Using ByVal
I have two columns of data in C & E. C is for a dollar amount, E is for a
percentage amount. Only ONE of the columns can have data at a time. I'm trying to set up a ByVal on the sheet so that if data is entered in column C, the data in the corresponding cell in column E would automatically be deleted and vice versa. The range right now is C4:C22 and E4:E22, but could be expanded (I can make that change manually, but it would be nice to have the ranges be named to so when changes are made, no change to the code is necessary. I have the ByVal code with a range, but don't know how to set up the code to delete the corresponding cell value. I would appreciate help with this. David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Corresponding Cell Using ByVal
Put this in the worksheet change event of the worksheet you are changing.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myCRange As Excel.Range Dim myERange As Excel.Range Dim myRange As Excel.Range If Target.Count 1 Then Exit Sub Set myCRange = Me.Range("C4:C22") Set myERange = myCRange.Offset(0, 2) Set myRange = Union(myCRange, myERange) If Not Intersect(Target, myRange) Is Nothing Then Application.EnableEvents = False If Not Intersect(Target, myCRange) Is Nothing Then Target.Offset(0, 2).ClearContents ElseIf Not Intersect(Target, myERange) Is Nothing Then Target.Offset(0, -2).ClearContents End If Application.EnableEvents = True End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub -- HTH, Barb Reinhardt "David" wrote: I have two columns of data in C & E. C is for a dollar amount, E is for a percentage amount. Only ONE of the columns can have data at a time. I'm trying to set up a ByVal on the sheet so that if data is entered in column C, the data in the corresponding cell in column E would automatically be deleted and vice versa. The range right now is C4:C22 and E4:E22, but could be expanded (I can make that change manually, but it would be nice to have the ranges be named to so when changes are made, no change to the code is necessary. I have the ByVal code with a range, but don't know how to set up the code to delete the corresponding cell value. I would appreciate help with this. David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Corresponding Cell Using ByVal
Hi,
Right click your sheet tab, view code and paste this in. Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRowC As Long, LastRowE As Long If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub LastRowC = Cells(Cells.Rows.Count, "C").End(xlUp).Row LastRowE = Cells(Cells.Rows.Count, "A").End(xlUp).Row If Not Intersect(Target, _ Range("C4:C" & LastRowC, "E4:E" & LastRowE)) Is Nothing Then Application.EnableEvents = False If Target.Column = 3 Then Target.Offset(, 2).ClearContents If Target.Column = 5 Then Target.Offset(, -2).ClearContents Application.EnableEvents = True End If End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "David" wrote: I have two columns of data in C & E. C is for a dollar amount, E is for a percentage amount. Only ONE of the columns can have data at a time. I'm trying to set up a ByVal on the sheet so that if data is entered in column C, the data in the corresponding cell in column E would automatically be deleted and vice versa. The range right now is C4:C22 and E4:E22, but could be expanded (I can make that change manually, but it would be nice to have the ranges be named to so when changes are made, no change to the code is necessary. I have the ByVal code with a range, but don't know how to set up the code to delete the corresponding cell value. I would appreciate help with this. David |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Corresponding Cell Using ByVal
oops,
an error, try this instead Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRowC As Long, LastRowE As Long If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub LastRowC = Cells(Cells.Rows.Count, "C").End(xlUp).Row LastRowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row If Not Intersect(Target, _ Range("C4:C" & LastRowC, "E4:E" & LastRowE)) Is Nothing Then Application.EnableEvents = False If Target.Column = 3 Then Target.Offset(, 2).ClearContents If Target.Column = 5 Then Target.Offset(, -2).ClearContents Application.EnableEvents = True End If End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in. Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRowC As Long, LastRowE As Long If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub LastRowC = Cells(Cells.Rows.Count, "C").End(xlUp).Row LastRowE = Cells(Cells.Rows.Count, "A").End(xlUp).Row If Not Intersect(Target, _ Range("C4:C" & LastRowC, "E4:E" & LastRowE)) Is Nothing Then Application.EnableEvents = False If Target.Column = 3 Then Target.Offset(, 2).ClearContents If Target.Column = 5 Then Target.Offset(, -2).ClearContents Application.EnableEvents = True End If End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "David" wrote: I have two columns of data in C & E. C is for a dollar amount, E is for a percentage amount. Only ONE of the columns can have data at a time. I'm trying to set up a ByVal on the sheet so that if data is entered in column C, the data in the corresponding cell in column E would automatically be deleted and vice versa. The range right now is C4:C22 and E4:E22, but could be expanded (I can make that change manually, but it would be nice to have the ranges be named to so when changes are made, no change to the code is necessary. I have the ByVal code with a range, but don't know how to set up the code to delete the corresponding cell value. I would appreciate help with this. David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Corresponding Cell Using ByVal
Here is another event code procedure that will do what you asked...
Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range For Each Cell In Target If Len(Cell.Value) 0 Then If Cell.Column = 3 Then Cell.Offset(0, 2).ClearContents If Cell.Column = 5 Then Cell.Offset(0, -2).ClearContents End If Next End Sub -- Rick (MVP - Excel) "David" wrote in message ... I have two columns of data in C & E. C is for a dollar amount, E is for a percentage amount. Only ONE of the columns can have data at a time. I'm trying to set up a ByVal on the sheet so that if data is entered in column C, the data in the corresponding cell in column E would automatically be deleted and vice versa. The range right now is C4:C22 and E4:E22, but could be expanded (I can make that change manually, but it would be nice to have the ranges be named to so when changes are made, no change to the code is necessary. I have the ByVal code with a range, but don't know how to set up the code to delete the corresponding cell value. I would appreciate help with this. David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change(ByVal... not working now | Excel Programming | |||
ByRef vs ByVal | Excel Programming | |||
ByVal? | Excel Programming | |||
what does (ByVal Target As Range) mean | Excel Programming | |||
Is ByVal always better if ByRef isn't necessary | Excel Programming |