| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy cells to neighbor column after leaving the cell
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy cells to neighbor column after leaving the cell
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" 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy cells to neighbor column after leaving the cell
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" 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy cells to neighbor column after leaving the cell
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" 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" 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Copy cells to neighbor column after leaving the cell
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" 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" 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy the value of a cell, no format, into several cells. | Excel Discussion (Misc queries) | |||
| Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
| Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
| I want to split containt of a cell and copy in seprate cells | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Linear Mode
