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

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

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

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

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

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