Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cells in range
If any cell (with value 0) in the range L2:T10 is selected I wish to update
the corresponding cell in the range B13:J21 with the value in $P$1. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cells in range
have I understood your question???
in B13 type =IF(L2<0,$P$1,"") copy B13 down and right upto J21 see whether you get what you want ============================ gocats wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cells in range
Unfortunately no, as I when i select say L2, have it populated from B13
which is set to $P$1. "R.VENKATARAMAN" wrote: have I understood your question??? in B13 type =IF(L2<0,$P$1,"") copy B13 down and right upto J21 see whether you get what you want ============================ gocats wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cells in range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If 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 RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cells in range
Thanks Bob
Absolutly brilliant! "Bob Phillips" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If 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 RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cells in range
How do I include a second range "Vl2:AD10" in the string and Me.Range("Z1")
and double click to set the active cell to 0? "gocats" wrote: Thanks Bob Absolutly brilliant! "Bob Phillips" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If 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 RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cells in range
I don't quite understand what you want to do with Z1, but here is the bit on
a second range and setting to 0 on double-click. Private Const WS_RANGE As String = "L2:T10,V12:AD10" Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) On Error GoTo wb_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Value = 0 End With End If wb_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... How do I include a second range "Vl2:AD10" in the string and Me.Range("Z1") and double click to set the active cell to 0? "gocats" wrote: Thanks Bob Absolutly brilliant! "Bob Phillips" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If 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 RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cells in range
Thanks Bob, that works well.
Unfortunately these things grow. I also have macros in the same range that now do not work. i.e. either the VB code works or the macros work. What do I need to do? "Bob Phillips" wrote: I don't quite understand what you want to do with Z1, but here is the bit on a second range and setting to 0 on double-click. Private Const WS_RANGE As String = "L2:T10,V12:AD10" Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) On Error GoTo wb_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Value = 0 End With End If wb_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... How do I include a second range "Vl2:AD10" in the string and Me.Range("Z1") and double click to set the active cell to 0? "gocats" wrote: Thanks Bob Absolutly brilliant! "Bob Phillips" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If 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 RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cells in range
Not sure I get the picture.
What macros, and why do they not work? -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... Thanks Bob, that works well. Unfortunately these things grow. I also have macros in the same range that now do not work. i.e. either the VB code works or the macros work. What do I need to do? "Bob Phillips" wrote: I don't quite understand what you want to do with Z1, but here is the bit on a second range and setting to 0 on double-click. Private Const WS_RANGE As String = "L2:T10,V12:AD10" Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) On Error GoTo wb_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Value = 0 End With End If wb_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... How do I include a second range "Vl2:AD10" in the string and Me.Range("Z1") and double click to set the active cell to 0? "gocats" wrote: Thanks Bob Absolutly brilliant! "Bob Phillips" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If 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 RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
Clearly seeing active cell in a range | Excel Discussion (Misc queries) | |||
Active range/selection? | Excel Worksheet Functions | |||
Filter range of only active cells??? | Excel Worksheet Functions | |||
Sum active range | Excel Programming |