Copy cells to neighbor column after leaving the cell

[nemadrias]

Hi -
Can anyone help me figure this out? I think I need a "With" loop, but
haven't really worked with them yet.

I have a column of empty cells. What I want to be able to do is copy
whatever is typed in any of those cells to the cell immediately to the
right of them in the column immediately to the right. I need to be
able to do this in the event (leaveCell) or whatever the event would be
called, basically as soon as the cursor leaves the cell (enter, tab,
click, whatever....) Is there a good way to do this?? Thanks a ton!
Steve

 

[Gord Dibben]

By formula...........

In B1 enter =IF(A1="","",A1)

Drag/copy down column B as far as you wish.

Event code..................

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Excel.Range("A" & n).Value
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".

Copy/paste the code into that module.


Gord Dibben MS Excel MVP

 

[nemadrias]

FANTASTIC!!! Thank you so much - the code is what I needed. Out of
curiosity can you Pseudocode the if loop for me if you have a quick
second? I don't quite understand how it works. Thanks again, keep up
the great work.
Steve

 

[Gord Dibben]

Not sure what Pseudocode is but.........

If Target.Cells.Column =1 means column A

n =TargetRow means any row in Column A

If Excel.Range("A" & n).Value <> "" means if An is not empty then stuff the
value of An into Bn as you leave An

Excel.Range("B" & n).Value = Excel.Range("A" & n).Value

...................................................................

=IF(A1="","",A1)

The formula method simply reads If A1 is empty, have B1 look empty also but if
A1 has a value, return that to B1


Gord

FANTASTIC!!! Thank you so much - the code is what I needed. Out of
curiosity can you Pseudocode the if loop for me if you have a quick
second? I don't quite understand how it works. Thanks again, keep up
the great work.
Steve

Gord Dibben MS Excel MVP

 

[nemadrias]

Pseudocode is just any code in layman's terms....exactly as you did.
Thanks again for all your help.
Steve