Thanks so much fr a quick reply. However, I just put in some code in
VB a
few minutes ago and I am getting a Compile Error: Ambiguous Name error
message. I notice that some of the code I copied to run a different macro in
the same sheet has the same line:
Private Sub Worksheet_Change (ByVal Target As Range).
What do I do so I can work the two macros etc?
The other code for the sheet follows:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'only one cell at a time!
If Target.Cells.Count 1 Then Exit Sub
'sort only if the change was in column P
If Intersect(Target, Me.Range("B3:Q52")) Is Nothing Then Exit Sub
With Me.Range("B3:Q52")
.Sort key1:=.Columns(15), order1:=xlDescending, _
key2:=.Columns(1), order2:=xlAscending, _
key3:=.Columns(2), order3:=xlAscending, _
header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub
THANKS!!
"Bob Phillips" wrote:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "O3:O52" '<== 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, 1).Value = Application.Sum(.Offset(0, -10).Resize(1,
11))
Me.Range("E3:P52").Sort key1:=Me.Range("P3"), _
order1:=xlDescending, _
header:=xlNo
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
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"Wayne4js" wrote in message
...
How can I calculate the sum of a row (E3:O3) in cell P3 ONLY after I enter
the value in the last cell in the row, O3? I want to enter values in the
cells E3:O3 but don't want the sum to calculate in P3 until after I exit
cell
O3.
Other notes:
1. All cells, including O3, are set to a 0 value
2. There are 50 rows, E3:E52
3. After the value in P3 is calculated, a sort occurs moving the data from
the row based on value (100 max) in descending order.
ANY HELP WOULD BE GREATLY APPRECIATED!! I have been searching discussion
pages, office help etc for hours and nothing. Guess I don't have the
terminology down yet!