VB problem

[FiluDlidu]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

 

[FiluDlidu]

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]

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

 

[FiluDlidu]

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.