| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can it be done in excel?
Does anyone have any suggestions on how to do this trick in excel?
Within the cell A1, B1, C1, when I enter a number in one of three cell, such as 3 in cell A1, then 3 will be automatically display on cell B1 and C1. On the next step, if I enter 6 in cell B1, then 6 will be automatically display on cell A1 and C1. On the next step, if I enter 8 in cell C1, then 8 will be automatically display on cell A1 and B1. Does anyone have any suggestions on how to do this trick in excel? Thanks in advance for any suggestions Eric |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can it be done in excel?
Very simple:
Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1:C1") If Intersect(r, Target) Is Nothing Then Exit Sub Application.EnableEvents = False r.Value = Target.Value Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200756 "Eric" wrote: Does anyone have any suggestions on how to do this trick in excel? Within the cell A1, B1, C1, when I enter a number in one of three cell, such as 3 in cell A1, then 3 will be automatically display on cell B1 and C1. On the next step, if I enter 6 in cell B1, then 6 will be automatically display on cell A1 and C1. On the next step, if I enter 8 in cell C1, then 8 will be automatically display on cell A1 and B1. Does anyone have any suggestions on how to do this trick in excel? Thanks in advance for any suggestions Eric |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can it be done in excel?
On Fri, 16 Nov 2007 06:09:03 -0800, Eric
wrote: Does anyone have any suggestions on how to do this trick in excel? Within the cell A1, B1, C1, when I enter a number in one of three cell, such as 3 in cell A1, then 3 will be automatically display on cell B1 and C1. On the next step, if I enter 6 in cell B1, then 6 will be automatically display on cell A1 and C1. On the next step, if I enter 8 in cell C1, then 8 will be automatically display on cell A1 and B1. Does anyone have any suggestions on how to do this trick in excel? Thanks in advance for any suggestions Eric Right click on the sheet tab. Select View Code Paste the code below into the window that opens: ======================================= Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim AOI As Range Set AOI = Range("A1:C1") Dim c As Range If Not Intersect(Target, AOI) Is Nothing Then Application.EnableEvents = False For Each c In AOI c.Value = Target.Value Next c Application.EnableEvents = True End If End Sub ==================================== --ron |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can it be done in excel?
Thank everyone very much for suggestions
Could anyone tell me how to modify Range("A1:C1") into different cells? such as A13, A67, A100. Thank everyone for any suggestions Eric "Sandy Mann" wrote: In a sheet module enter: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("A1:C1")) Is Nothing Then Exit Sub Application.EnableEvents = False Range("A1:C1").Value = Target.Value Application.EnableEvents = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Eric" wrote in message ... Does anyone have any suggestions on how to do this trick in excel? Within the cell A1, B1, C1, when I enter a number in one of three cell, such as 3 in cell A1, then 3 will be automatically display on cell B1 and C1. On the next step, if I enter 6 in cell B1, then 6 will be automatically display on cell A1 and C1. On the next step, if I enter 8 in cell C1, then 8 will be automatically display on cell A1 and B1. Does anyone have any suggestions on how to do this trick in excel? Thanks in advance for any suggestions Eric |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can it be done in excel?
Thank everyone very much for suggestions
I would like refer to specific cells rather than range. Could anyone tell me how to modify Range("A1:C1") into different cells? such as A13, A67, A100. Thank everyone for any suggestions Eric "Ron Rosenfeld" wrote: On Fri, 16 Nov 2007 06:09:03 -0800, Eric wrote: Does anyone have any suggestions on how to do this trick in excel? Within the cell A1, B1, C1, when I enter a number in one of three cell, such as 3 in cell A1, then 3 will be automatically display on cell B1 and C1. On the next step, if I enter 6 in cell B1, then 6 will be automatically display on cell A1 and C1. On the next step, if I enter 8 in cell C1, then 8 will be automatically display on cell A1 and B1. Does anyone have any suggestions on how to do this trick in excel? Thanks in advance for any suggestions Eric Right click on the sheet tab. Select View Code Paste the code below into the window that opens: ======================================= Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim AOI As Range Set AOI = Range("A1:C1") Dim c As Range If Not Intersect(Target, AOI) Is Nothing Then Application.EnableEvents = False For Each c In AOI c.Value = Target.Value Next c Application.EnableEvents = True End If End Sub ==================================== --ron |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can it be done in excel?
Thank everyone very much for suggestions
I would like refer to specific cells rather than range. Could anyone tell me how to modify Range("A1:C1") into different cells? such as A13, A67, A100. Thank everyone for any suggestions Eric "Gary''s Student" wrote: Very simple: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1:C1") If Intersect(r, Target) Is Nothing Then Exit Sub Application.EnableEvents = False r.Value = Target.Value Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200756 "Eric" wrote: Does anyone have any suggestions on how to do this trick in excel? Within the cell A1, B1, C1, when I enter a number in one of three cell, such as 3 in cell A1, then 3 will be automatically display on cell B1 and C1. On the next step, if I enter 6 in cell B1, then 6 will be automatically display on cell A1 and C1. On the next step, if I enter 8 in cell C1, then 8 will be automatically display on cell A1 and B1. Does anyone have any suggestions on how to do this trick in excel? Thanks in advance for any suggestions Eric |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can it be done in excel?
We only need to change a single line of code to use any three disjoint cells:
Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A13,A67,A100") If Intersect(r, Target) Is Nothing Then Exit Sub Application.EnableEvents = False r.Value = Target.Value Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200756 "Eric" wrote: Thank everyone very much for suggestions I would like refer to specific cells rather than range. Could anyone tell me how to modify Range("A1:C1") into different cells? such as A13, A67, A100. Thank everyone for any suggestions Eric "Gary''s Student" wrote: Very simple: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1:C1") If Intersect(r, Target) Is Nothing Then Exit Sub Application.EnableEvents = False r.Value = Target.Value Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200756 "Eric" wrote: Does anyone have any suggestions on how to do this trick in excel? Within the cell A1, B1, C1, when I enter a number in one of three cell, such as 3 in cell A1, then 3 will be automatically display on cell B1 and C1. On the next step, if I enter 6 in cell B1, then 6 will be automatically display on cell A1 and C1. On the next step, if I enter 8 in cell C1, then 8 will be automatically display on cell A1 and B1. Does anyone have any suggestions on how to do this trick in excel? Thanks in advance for any suggestions Eric |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Can it be done in excel?
On Fri, 16 Nov 2007 20:34:01 -0800, Eric
wrote: Thank everyone very much for suggestions I would like refer to specific cells rather than range. Could anyone tell me how to modify Range("A1:C1") into different cells? such as A13, A67, A100. Thank everyone for any suggestions Eric Merely change the parameters to the Range Property. In my contribution, it would be: Set AOI = Range("A13,A67,A100") --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
