Here's a tricky one:

I would like an action to occur each time text is entered into a cell
in Column A. This needs to be done using VB, not a macro. When data is
entered into, say, cell A2, I would like the formulas in cells C2:E2
to be copied down into cells C3:E3.

The purpose of this is to avoid having formulas in cells until they
are needed (i.e. each time a row with formulas is used, a new blank
row with formulas is created).

In doing a web search for a solution, I found the following code
(props to Gord Dibben, circa 2003):

Copy/paste this Event code to your worksheet module.
Select Sheet TabView Code.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("B" & n).Value < "" Then
Excel.Range("C" & n).Value = Excel.Range("B" & n).Value
End If
End If
enditall:
End Sub

Unfortunately, I am unable to adapt this to my specific
needs...anybody care to take a swing?

Maybe this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Range("A" & n).Value < "" _
Then
Range("C" & (n - 1) & ":E" & (n - 1)).Copy
Range("C" & n).Select
ActiveSheet.Paste
Else
End If
End If
Range("A" & n + 1).Select
enditall:
End Sub


HTH,
Paul

"Shonzi" wrote in message
ps.com...
Here's a tricky one:

I would like an action to occur each time text is entered into a cell
in Column A. This needs to be done using VB, not a macro. When data is
entered into, say, cell A2, I would like the formulas in cells C2:E2
to be copied down into cells C3:E3.

The purpose of this is to avoid having formulas in cells until they
are needed (i.e. each time a row with formulas is used, a new blank
row with formulas is created).

In doing a web search for a solution, I found the following code
(props to Gord Dibben, circa 2003):

Copy/paste this Event code to your worksheet module.
Select Sheet TabView Code.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("B" & n).Value < "" Then
Excel.Range("C" & n).Value = Excel.Range("B" & n).Value
End If
End If
enditall:
End Sub

Unfortunately, I am unable to adapt this to my specific
needs...anybody care to take a swing?

On Apr 12, 5:10 pm, "PCLIVE" wrote:
Maybe this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Range("A" & n).Value < "" _
Then
Range("C" & (n - 1) & ":E" & (n - 1)).Copy
Range("C" & n).Select
ActiveSheet.Paste
Else
End If
End If
Range("A" & n + 1).Select
enditall:
End Sub

HTH,
Paul

"Shonzi" wrote in message

ps.com...



Here's a tricky one:

I would like an action to occur each time text is entered into a cell
in Column A. This needs to be done using VB, not a macro. When data is
entered into, say, cell A2, I would like the formulas in cells C2:E2
to be copied down into cells C3:E3.

The purpose of this is to avoid having formulas in cells until they
are needed (i.e. each time a row with formulas is used, a new blank
row with formulas is created).

In doing a web search for a solution, I found the following code
(props to Gord Dibben, circa 2003):

Copy/paste this Event code to your worksheet module.
Select Sheet TabView Code.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("B" & n).Value < "" Then
Excel.Range("C" & n).Value = Excel.Range("B" & n).Value
End If
End If
enditall:
End Sub

Unfortunately, I am unable to adapt this to my specific
needs...anybody care to take a swing?- Hide quoted text -

- Show quoted text -

That was close, but I had to modify it slightly...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Range("A" & n).Value < "" _
Then
Range("C" & n & ":E" & n).Copy
Range("C" & (n + 1)).Select
ActiveSheet.Paste
Else
End If
End If
Range("A" & n + 1).Select
enditall:
End Sub

Thank you! Couldn't have done it alone... :-)

On Apr 12, 5:33 pm, "Shonzi" wrote:
On Apr 12, 5:10 pm, "PCLIVE" wrote:





Maybe this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Range("A" & n).Value < "" _
Then
Range("C" & (n - 1) & ":E" & (n - 1)).Copy
Range("C" & n).Select
ActiveSheet.Paste
Else
End If
End If
Range("A" & n + 1).Select
enditall:
End Sub

HTH,
Paul

"Shonzi" wrote in message

ups.com...

Here's a tricky one:

I would like an action to occur each time text is entered into a cell
in Column A. This needs to be done using VB, not a macro. When data is
entered into, say, cell A2, I would like the formulas in cells C2:E2
to be copied down into cells C3:E3.

The purpose of this is to avoid having formulas in cells until they
are needed (i.e. each time a row with formulas is used, a new blank
row with formulas is created).

In doing a web search for a solution, I found the following code
(props to Gord Dibben, circa 2003):

Copy/paste this Event code to your worksheet module.
Select Sheet TabView Code.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("B" & n).Value < "" Then
Excel.Range("C" & n).Value = Excel.Range("B" & n).Value
End If
End If
enditall:
End Sub

Unfortunately, I am unable to adapt this to my specific
needs...anybody care to take a swing?- Hide quoted text -

- Show quoted text -

That was close, but I had to modify it slightly...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Range("A" & n).Value < "" _
Then
Range("C" & n & ":E" & n).Copy
Range("C" & (n + 1)).Select
ActiveSheet.Paste
Else
End If
End If
Range("A" & n + 1).Select
enditall:
End Sub

Thank you! Couldn't have done it alone... :-)- Hide quoted text -

- Show quoted text -

So, with the above code I am getting two undesirable results:

1. The macro window appears when you open the sheet (I was hoping to
avoid this by using VB, but it appears there is no way around that...)

2. Every time you change a cell that is NOT in Column A, the focus
returns to cell A1. For example, if I am in cell B200 and I make a
change, I am thrown rather abruptly up to A1 upon hitting enter or
tab. It is less jarring with the windows frozen, so that Row 1 is
alway on screen, but it is a nuisance nonetheless...

Anyone have a solution to either of these issues??

Try moving the following line:

Range("A" & n + 1).Select

Move this above 'Else'.


"Shonzi" wrote in message
oups.com...
On Apr 12, 5:33 pm, "Shonzi" wrote:
On Apr 12, 5:10 pm, "PCLIVE" wrote:





Maybe this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Range("A" & n).Value < "" _
Then
Range("C" & (n - 1) & ":E" & (n - 1)).Copy
Range("C" & n).Select
ActiveSheet.Paste
Else
End If
End If
Range("A" & n + 1).Select
enditall:
End Sub

HTH,
Paul

"Shonzi" wrote in message

ups.com...

Here's a tricky one:

I would like an action to occur each time text is entered into a cell
in Column A. This needs to be done using VB, not a macro. When data
is
entered into, say, cell A2, I would like the formulas in cells C2:E2
to be copied down into cells C3:E3.

The purpose of this is to avoid having formulas in cells until they
are needed (i.e. each time a row with formulas is used, a new blank
row with formulas is created).

In doing a web search for a solution, I found the following code
(props to Gord Dibben, circa 2003):

Copy/paste this Event code to your worksheet module.
Select Sheet TabView Code.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("B" & n).Value < "" Then
Excel.Range("C" & n).Value = Excel.Range("B" & n).Value
End If
End If
enditall:
End Sub

Unfortunately, I am unable to adapt this to my specific
needs...anybody care to take a swing?- Hide quoted text -

- Show quoted text -

That was close, but I had to modify it slightly...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Range("A" & n).Value < "" _
Then
Range("C" & n & ":E" & n).Copy
Range("C" & (n + 1)).Select
ActiveSheet.Paste
Else
End If
End If
Range("A" & n + 1).Select
enditall:
End Sub

Thank you! Couldn't have done it alone... :-)- Hide quoted text -

- Show quoted text -

So, with the above code I am getting two undesirable results:

1. The macro window appears when you open the sheet (I was hoping to
avoid this by using VB, but it appears there is no way around that...)

2. Every time you change a cell that is NOT in Column A, the focus
returns to cell A1. For example, if I am in cell B200 and I make a
change, I am thrown rather abruptly up to A1 upon hitting enter or
tab. It is less jarring with the windows frozen, so that Row 1 is
alway on screen, but it is a nuisance nonetheless...

Anyone have a solution to either of these issues??

On Apr 13, 9:27 am, "PCLIVE" wrote:
Try moving the following line:

Range("A" & n + 1).Select

Move this above 'Else'.

"Shonzi" wrote in message

oups.com...



On Apr 12, 5:33 pm, "Shonzi" wrote:
On Apr 12, 5:10 pm, "PCLIVE" wrote:

Maybe this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Range("A" & n).Value < "" _
Then
Range("C" & (n - 1) & ":E" & (n - 1)).Copy
Range("C" & n).Select
ActiveSheet.Paste
Else
End If
End If
Range("A" & n + 1).Select
enditall:
End Sub

HTH,
Paul

"Shonzi" wrote in message

ups.com...

Here's a tricky one:

I would like an action to occur each time text is entered into a cell
in Column A. This needs to be done using VB, not a macro. When data
is
entered into, say, cell A2, I would like the formulas in cells C2:E2
to be copied down into cells C3:E3.

The purpose of this is to avoid having formulas in cells until they
are needed (i.e. each time a row with formulas is used, a new blank
row with formulas is created).

In doing a web search for a solution, I found the following code
(props to Gord Dibben, circa 2003):

Copy/paste this Event code to your worksheet module.
Select Sheet TabView Code.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("B" & n).Value < "" Then
Excel.Range("C" & n).Value = Excel.Range("B" & n).Value
End If
End If
enditall:
End Sub

Unfortunately, I am unable to adapt this to my specific
needs...anybody care to take a swing?- Hide quoted text -

- Show quoted text -

That was close, but I had to modify it slightly...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Range("A" & n).Value < "" _
Then
Range("C" & n & ":E" & n).Copy
Range("C" & (n + 1)).Select
ActiveSheet.Paste
Else
End If
End If
Range("A" & n + 1).Select
enditall:
End Sub

Thank you! Couldn't have done it alone... :-)- Hide quoted text -

- Show quoted text -

So, with the above code I am getting two undesirable results:

1. The macro window appears when you open the sheet (I was hoping to
avoid this by using VB, but it appears there is no way around that...)

2. Every time you change a cell that is NOT in Column A, the focus
returns to cell A1. For example, if I am in cell B200 and I make a
change, I am thrown rather abruptly up to A1 upon hitting enter or
tab. It is less jarring with the windows frozen, so that Row 1 is
alway on screen, but it is a nuisance nonetheless...

Anyone have a solution to either of these issues??- Hide quoted text -

- Show quoted text -


That did the trick! Nice job, thanks again. So now the code looks like
this...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Range("A" & n).Value < "" _
Then
Range("C" & n & ":E" & n).Copy
Range("C" & (n + 1)).Select
ActiveSheet.Paste
Range("A" & n + 1).Select
Else
End If
End If
enditall:
End Sub

There is still one problem, albeit a minor one. When entering data in
a cell in Column A, if you hit tab (to go to Column B) it bumps you
down to the next row instead, in Column A.

Is there any way to fix this?