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

On 11 Jul 2006 07:16:17 -0700, "nemadrias" <[email protected]> wrote:

>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

[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 wrote:
> 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
>
> On 11 Jul 2006 07:16:17 -0700, "nemadrias" <[email protected]> wrote:
>
> >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]

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

On 11 Jul 2006 08:44:54 -0700, "nemadrias" <[email protected]> wrote:

>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 wrote:
>> 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
>>
>> On 11 Jul 2006 07:16:17 -0700, "nemadrias" <[email protected]> wrote:
>>
>> >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 MS Excel MVP

[nemadrias]

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

Gord Dibben wrote:
> 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
>
> On 11 Jul 2006 08:44:54 -0700, "nemadrias" <[email protected]> wrote:
>
> >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 wrote:
> >> 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
> >>
> >> On 11 Jul 2006 07:16:17 -0700, "nemadrias" <[email protected]> wrote:
> >>
> >> >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 MS Excel MVP