Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset question
I asked the question below in the public.excel forum. The response I got
indicated that what I asked was not possible without using VBA. So is there any way using VBA to cause the result of the formula in cell D4 to also be displayed in cell B4. Again I can not have a formula in cell B4. "I need a formula in cell C4 which will place the value of cell D4 in cell B4. Cell B4 can contain no formula" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset question
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "D4" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0, -2).Value = .Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Patrick C. Simonds" wrote in message ... I asked the question below in the public.excel forum. The response I got indicated that what I asked was not possible without using VBA. So is there any way using VBA to cause the result of the formula in cell D4 to also be displayed in cell B4. Again I can not have a formula in cell B4. "I need a formula in cell C4 which will place the value of cell D4 in cell B4. Cell B4 can contain no formula" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset question
It seems what I actually need is:
When cell F4 changes, I need the Value in cell D4 placed in cell B4. "Bob Phillips" wrote in message ... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "D4" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0, -2).Value = .Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Patrick C. Simonds" wrote in message ... I asked the question below in the public.excel forum. The response I got indicated that what I asked was not possible without using VBA. So is there any way using VBA to cause the result of the formula in cell D4 to also be displayed in cell B4. Again I can not have a formula in cell B4. "I need a formula in cell C4 which will place the value of cell D4 in cell B4. Cell B4 can contain no formula" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset question
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "F4" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("D4").Copy Me.Range("B4") End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Patrick C. Simonds" wrote in message ... It seems what I actually need is: When cell F4 changes, I need the Value in cell D4 placed in cell B4. "Bob Phillips" wrote in message ... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "D4" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0, -2).Value = .Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Patrick C. Simonds" wrote in message ... I asked the question below in the public.excel forum. The response I got indicated that what I asked was not possible without using VBA. So is there any way using VBA to cause the result of the formula in cell D4 to also be displayed in cell B4. Again I can not have a formula in cell B4. "I need a formula in cell C4 which will place the value of cell D4 in cell B4. Cell B4 can contain no formula" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset question
Thanks, but that copies the formula in cell D4 to cell B4. I only want the
Value of cell D4 in B4 "Bob Phillips" wrote in message ... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "F4" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("D4").Copy Me.Range("B4") End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Patrick C. Simonds" wrote in message ... It seems what I actually need is: When cell F4 changes, I need the Value in cell D4 placed in cell B4. "Bob Phillips" wrote in message ... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "D4" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0, -2).Value = .Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Patrick C. Simonds" wrote in message ... I asked the question below in the public.excel forum. The response I got indicated that what I asked was not possible without using VBA. So is there any way using VBA to cause the result of the formula in cell D4 to also be displayed in cell B4. Again I can not have a formula in cell B4. "I need a formula in cell C4 which will place the value of cell D4 in cell B4. Cell B4 can contain no formula" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset question
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "F4" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("B4").Value = Me.Range("D4").Value End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Patrick C. Simonds" wrote in message ... Thanks, but that copies the formula in cell D4 to cell B4. I only want the Value of cell D4 in B4 "Bob Phillips" wrote in message ... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "F4" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then Me.Range("D4").Copy Me.Range("B4") End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Patrick C. Simonds" wrote in message ... It seems what I actually need is: When cell F4 changes, I need the Value in cell D4 placed in cell B4. "Bob Phillips" wrote in message ... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "D4" '<== change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Offset(0, -2).Value = .Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Patrick C. Simonds" wrote in message ... I asked the question below in the public.excel forum. The response I got indicated that what I asked was not possible without using VBA. So is there any way using VBA to cause the result of the formula in cell D4 to also be displayed in cell B4. Again I can not have a formula in cell B4. "I need a formula in cell C4 which will place the value of cell D4 in cell B4. Cell B4 can contain no formula" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset question | Excel Discussion (Misc queries) | |||
Offset Question | Excel Worksheet Functions | |||
Offset Question | Excel Programming | |||
Offset question | Excel Worksheet Functions | |||
Offset question | Excel Programming |