| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB problem
Hi all,
I'm looking for a code that would do the following: - When I press whichever data entry validation key combination(((("Enter", "Ctrl+Enter", "Shift+Enter", "Tab" or "Shift+Tab")))) following data entry into a cell from either B or C columns, then if... .... the cell-with-content count (COUNTA) for the same row of these two columns (B & C) is equal to 2; .... and the result of a formula included in equivalent cell in column D is not an error; .... and the cell of the same row in column A is empty. - Then copy the value of cell D into cell A of the same row... .... and go to the cell where the elected keystroke would have gotten me by default. - If the conditions were not to be met, then just do whatever is the default (exit sub, I guess...) Is this too much asking? I hope not and wish to thank in advance anyone who will put any amount of thinking, even fruitless, into this problem, Feelu |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB problem
Feelu,
Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub If Cells(Target.Row, 2).Value < "" And _ Cells(Target.Row, 3).Value < "" And _ Not IsError(Cells(Target.Row, 4).Value) Then Application.EnableEvents = False Cells(Target.Row, 1).Value = Cells(Target.Row, 4).Value Application.EnableEvents = False End If End Sub "FiluDlidu" wrote in message ... Hi all, I'm looking for a code that would do the following: - When I press whichever data entry validation key combination(((("Enter", "Ctrl+Enter", "Shift+Enter", "Tab" or "Shift+Tab")))) following data entry into a cell from either B or C columns, then if... ... the cell-with-content count (COUNTA) for the same row of these two columns (B & C) is equal to 2; ... and the result of a formula included in equivalent cell in column D is not an error; ... and the cell of the same row in column A is empty. - Then copy the value of cell D into cell A of the same row... ... and go to the cell where the elected keystroke would have gotten me by default. - If the conditions were not to be met, then just do whatever is the default (exit sub, I guess...) Is this too much asking? I hope not and wish to thank in advance anyone who will put any amount of thinking, even fruitless, into this problem, Feelu |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB problem
Ooops, forgot one condition (that A is currently empty...)
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub If Cells(Target.Row, 2).Value < "" And _ Cells(Target.Row, 3).Value < "" And _ Cells(Target.Row, 1).Value = "" And _ Not IsError(Cells(Target.Row, 4).Value) Then Application.EnableEvents = False Cells(Target.Row, 1).Value = Cells(Target.Row, 4).Value Application.EnableEvents = False End If End Sub HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Feelu, Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub If Cells(Target.Row, 2).Value < "" And _ Cells(Target.Row, 3).Value < "" And _ Not IsError(Cells(Target.Row, 4).Value) Then Application.EnableEvents = False Cells(Target.Row, 1).Value = Cells(Target.Row, 4).Value Application.EnableEvents = False End If End Sub "FiluDlidu" wrote in message ... Hi all, I'm looking for a code that would do the following: - When I press whichever data entry validation key combination(((("Enter", "Ctrl+Enter", "Shift+Enter", "Tab" or "Shift+Tab")))) following data entry into a cell from either B or C columns, then if... ... the cell-with-content count (COUNTA) for the same row of these two columns (B & C) is equal to 2; ... and the result of a formula included in equivalent cell in column D is not an error; ... and the cell of the same row in column A is empty. - Then copy the value of cell D into cell A of the same row... ... and go to the cell where the elected keystroke would have gotten me by default. - If the conditions were not to be met, then just do whatever is the default (exit sub, I guess...) Is this too much asking? I hope not and wish to thank in advance anyone who will put any amount of thinking, even fruitless, into this problem, Feelu |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB problem
Hi Bernie,
I'm not sure why, but it worked once, then systematically failed since that first success.\ Thanks a lot for your help anyway, Feelu "Bernie Deitrick" wrote: Ooops, forgot one condition (that A is currently empty...) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub If Cells(Target.Row, 2).Value < "" And _ Cells(Target.Row, 3).Value < "" And _ Cells(Target.Row, 1).Value = "" And _ Not IsError(Cells(Target.Row, 4).Value) Then Application.EnableEvents = False Cells(Target.Row, 1).Value = Cells(Target.Row, 4).Value Application.EnableEvents = False End If End Sub HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Feelu, Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub If Cells(Target.Row, 2).Value < "" And _ Cells(Target.Row, 3).Value < "" And _ Not IsError(Cells(Target.Row, 4).Value) Then Application.EnableEvents = False Cells(Target.Row, 1).Value = Cells(Target.Row, 4).Value Application.EnableEvents = False End If End Sub "FiluDlidu" wrote in message ... Hi all, I'm looking for a code that would do the following: - When I press whichever data entry validation key combination(((("Enter", "Ctrl+Enter", "Shift+Enter", "Tab" or "Shift+Tab")))) following data entry into a cell from either B or C columns, then if... ... the cell-with-content count (COUNTA) for the same row of these two columns (B & C) is equal to 2; ... and the result of a formula included in equivalent cell in column D is not an error; ... and the cell of the same row in column A is empty. - Then copy the value of cell D into cell A of the same row... ... and go to the cell where the elected keystroke would have gotten me by default. - If the conditions were not to be met, then just do whatever is the default (exit sub, I guess...) Is this too much asking? I hope not and wish to thank in advance anyone who will put any amount of thinking, even fruitless, into this problem, Feelu |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB problem
It's cause I'm a dunce.... the second instance of Application.EnableEvents = False should have been Application.EnableEvents = True Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub If Cells(Target.Row, 2).Value < "" And _ Cells(Target.Row, 3).Value < "" And _ Cells(Target.Row, 1).Value = "" And _ Not IsError(Cells(Target.Row, 4).Value) Then Application.EnableEvents = False Cells(Target.Row, 1).Value = Cells(Target.Row, 4).Value Application.EnableEvents = True End If End Sub -- HTH, Bernie MS Excel MVP "FiluDlidu" wrote in message ... Hi Bernie, I'm not sure why, but it worked once, then systematically failed since that first success.\ Thanks a lot for your help anyway, Feelu "Bernie Deitrick" wrote: Ooops, forgot one condition (that A is currently empty...) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub If Cells(Target.Row, 2).Value < "" And _ Cells(Target.Row, 3).Value < "" And _ Cells(Target.Row, 1).Value = "" And _ Not IsError(Cells(Target.Row, 4).Value) Then Application.EnableEvents = False Cells(Target.Row, 1).Value = Cells(Target.Row, 4).Value Application.EnableEvents = False End If End Sub HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Feelu, Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub If Cells(Target.Row, 2).Value < "" And _ Cells(Target.Row, 3).Value < "" And _ Not IsError(Cells(Target.Row, 4).Value) Then Application.EnableEvents = False Cells(Target.Row, 1).Value = Cells(Target.Row, 4).Value Application.EnableEvents = False End If End Sub "FiluDlidu" wrote in message ... Hi all, I'm looking for a code that would do the following: - When I press whichever data entry validation key combination(((("Enter", "Ctrl+Enter", "Shift+Enter", "Tab" or "Shift+Tab")))) following data entry into a cell from either B or C columns, then if... ... the cell-with-content count (COUNTA) for the same row of these two columns (B & C) is equal to 2; ... and the result of a formula included in equivalent cell in column D is not an error; ... and the cell of the same row in column A is empty. - Then copy the value of cell D into cell A of the same row... ... and go to the cell where the elected keystroke would have gotten me by default. - If the conditions were not to be met, then just do whatever is the default (exit sub, I guess...) Is this too much asking? I hope not and wish to thank in advance anyone who will put any amount of thinking, even fruitless, into this problem, Feelu |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VB problem
I'm not too sure about what a dunce is (my first language is not English),
but it doesn't sound too good and I think you should be more respectful than that to yourself, for you've been immensely helpful to me. Your correction worked! Thanks a lot again. "Bernie Deitrick" wrote: It's cause I'm a dunce.... the second instance of Application.EnableEvents = False should have been Application.EnableEvents = True Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub If Cells(Target.Row, 2).Value < "" And _ Cells(Target.Row, 3).Value < "" And _ Cells(Target.Row, 1).Value = "" And _ Not IsError(Cells(Target.Row, 4).Value) Then Application.EnableEvents = False Cells(Target.Row, 1).Value = Cells(Target.Row, 4).Value Application.EnableEvents = True End If End Sub -- HTH, Bernie MS Excel MVP "FiluDlidu" wrote in message ... Hi Bernie, I'm not sure why, but it worked once, then systematically failed since that first success.\ Thanks a lot for your help anyway, Feelu "Bernie Deitrick" wrote: Ooops, forgot one condition (that A is currently empty...) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub If Cells(Target.Row, 2).Value < "" And _ Cells(Target.Row, 3).Value < "" And _ Cells(Target.Row, 1).Value = "" And _ Not IsError(Cells(Target.Row, 4).Value) Then Application.EnableEvents = False Cells(Target.Row, 1).Value = Cells(Target.Row, 4).Value Application.EnableEvents = False End If End Sub HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Feelu, Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub If Cells(Target.Row, 2).Value < "" And _ Cells(Target.Row, 3).Value < "" And _ Not IsError(Cells(Target.Row, 4).Value) Then Application.EnableEvents = False Cells(Target.Row, 1).Value = Cells(Target.Row, 4).Value Application.EnableEvents = False End If End Sub "FiluDlidu" wrote in message ... Hi all, I'm looking for a code that would do the following: - When I press whichever data entry validation key combination(((("Enter", "Ctrl+Enter", "Shift+Enter", "Tab" or "Shift+Tab")))) following data entry into a cell from either B or C columns, then if... ... the cell-with-content count (COUNTA) for the same row of these two columns (B & C) is equal to 2; ... and the result of a formula included in equivalent cell in column D is not an error; ... and the cell of the same row in column A is empty. - Then copy the value of cell D into cell A of the same row... ... and go to the cell where the elected keystroke would have gotten me by default. - If the conditions were not to be met, then just do whatever is the default (exit sub, I guess...) Is this too much asking? I hope not and wish to thank in advance anyone who will put any amount of thinking, even fruitless, into this problem, Feelu |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
| problem with a conditional max problem | Excel Discussion (Misc queries) | |||
Linear Mode
