Home 
Search 
Today's Posts 
#1




Worksheet_BeforeDoubleClick subtract sum of cells below top cell
Hello All,
My first post on this forum. In cells B2 to X21 I have a range of calculations that I use to work out the axle spacingâ€™s of trucks. Column B Column C Column D Column E Column F Column G 3885 1370 8200 1220 1220 = 4810 + 1000 = 1370/2 50 1220 3200 = 1370/2 = 1380/2 1220 3900 6245 Whe Axle 1 to 2 = 3885  685 = 3200 Axle 2 to 3 = 1370 Axle 3 to 4 = 8200  50  685  1220 = 6245 OR 8200  SUM(D35) = 6245 Axle 4 to 5 = 1220 Axle 5 to 6 = 1220 Axle 6 to 7 = 5810  1220  690 = 3900 OR 5810  SUM(G3:G4) = 3900 The first/top cell is the overall spacing between the axle groups. A truck operator may just provide the spacing to the centre of the dolly or truck body, which means I need to work out the spacing for each of the axle groups. I have found many, many instances of Worksheet_BeforeDoubleClick macros where you select the cell below and the double click will SUM all of the values above to a blank cell. As some of my spacing calculations are formulas some of these SUM macros do not work. I have other calculations below the range B2 to X21 so I would like to restrict the Worksheet_BeforeDoubleClick to just this range and also only apply to the double clicked cell. The Worksheet_BeforeDoubleClick should use the top/first cell of each contiguous range and subtract the sum of all the values below. I am hoping this all makes sense. Regards, Dave 
#2




Worksheet_BeforeDoubleClick subtract sum of cells below top cell
Dave,
I think you will need to explain much more clearly what you want to do. I sort of got the gist but not enough to even think making a suggestion. Peter T 
#3




Worksheet_BeforeDoubleClick subtract sum of cells below top cell
On Friday, January 1, 2021 at 1:29:22 AM UTC+10:30, Peter T wrote:
Dave, I think you will need to explain much more clearly what you want to do. I sort of got the gist but not enough to even think making a suggestion. Peter T Hello Peter, I realised this probably did not make sense so I thought I had deleted the post, so I was surprised to get a reply. What I had been trying to achieve was a Worksheet_BeforeDoubleClick macro where you could double click in a blank below a range of contiguous values and it would insert a formula that would take the range of cells above the active cell formula and below the top value (below a blank cell) and subtract them from the top value, something like: (top cell) 5,810 minus (next cell down) 1,220 minus (next cell down) 690 minus (next cell down) 1,000 = 2,900 = 5,180  1,220  (1,380 / 2)  (500 + 500) = 2,900 Which is effectively subtracting every value from the first/top value, but these contiguous cells/rows vary in size. The other way of looking at it is effectively subtracting the SUM of all the values below the first/top cell from the first/top cell, but not using specific cell references written into the macro. As the column may contain multiple contiguous groups I wanted to restrict the Worksheet_BeforeDoubleclick macro to individual contiguous groups and not the whole column. I am hoping the macro will not be column or row specific, but be able to be used anywhere on the worksheet and not affected by any other contiguous groups in a column. I hope this explanation make more sense ? Regards, Dave 
#4




Worksheet_BeforeDoubleClick subtract sum of cells below top cell
"Dave" wrote in message
... On Friday, January 1, 2021 at 1:29:22 AM UTC+10:30, Peter T wrote: Dave, I think you will need to explain much more clearly what you want to do. I sort of got the gist but not enough to even think making a suggestion. Peter T Hello Peter, I realised this probably did not make sense so I thought I had deleted the post, so I was surprised to get a reply. What I had been trying to achieve was a Worksheet_BeforeDoubleClick macro where you could double click in a blank below a range of contiguous values and it would insert a formula that would take the range of cells above the active cell formula and below the top value (below a blank cell) and subtract them from the top value, something like: (top cell) 5,810 minus (next cell down) 1,220 minus (next cell down) 690 minus (next cell down) 1,000 = 2,900 = 5,180  1,220  (1,380 / 2)  (500 + 500) = 2,900 Which is effectively subtracting every value from the first/top value, but these contiguous cells/rows vary in size. The other way of looking at it is effectively subtracting the SUM of all the values below the first/top cell from the first/top cell, but not using specific cell references written into the macro. As the column may contain multiple contiguous groups I wanted to restrict the Worksheet_BeforeDoubleclick macro to individual contiguous groups and not the whole column. I am hoping the macro will not be column or row specific, but be able to be used anywhere on the worksheet and not affected by any other contiguous groups in a column. I hope this explanation make more sense ? Regards, Dave ================================================== ======== I'm still not quite sure if I follow, particularly what defines the top cell in all scenarios, but try this ' in the Worksheet module Option Explicit Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) If Target.Count 1 Then ' multiple cells selected ElseIf IsEmpty(Target(0, 1)) Then ' the cell above is empty ElseIf IsEmpty(Target(1, 1)) Then ' need at least two cells above to sum ??? ElseIf IsEmpty(Target) Or Target.HasFormula Then ' the cell is empty or has a formula (to be amended) Cancel = True ' cancel the context menu AddFormula Target End If End Sub Sub AddFormula(rCell As Range) Dim lFirstSumRow, lSumRows As Long, lTopCellRow As Long Dim sSumAddr As String, sTopCellAddr As String Dim sFml As String Dim rCellTmp As Range With rCell ' first row of the contiguous cells above lFirstSumRow = .Cells(0, 1).End(xlUp).Row ' count rows to sum = row of formula row  first row in sum range lSumRows = .Row  lFirstSumRow ' the sum address sSumAddr = .Offset(lSumRows).Resize(lSumRows).Address(False, False) ' sum formula sFml = "Sum(" & sSumAddr & ")" ' define the top sumrange cell Set rCellTmp = Cells(lFirstSumRow, .Column) lTopCellRow = rCellTmp.End(xlUp).Row If lTopCellRow 1 Then ' assume the topcell must be below row1 ? sTopCellAddr = Cells(lTopCellRow, .Column).Address(False, False) sFml = sTopCellAddr & "  " & sFml .Formula = "=" & sFml ' apply the formula .Font.Color = vbBlue ' optional font colour End If End With End Sub ''''''''''''''''''''''''''' Several ways of doing this but tried to make it easy to follow, adapt if/as required. Address(False,False) returns the Relative address, adapt to return Absolute Peter T 
#5




Worksheet_BeforeDoubleClick subtract sum of cells below top cell
On 1/2/2021 7:53 AM, Dave wrote:
On Friday, January 1, 2021 at 1:29:22 AM UTC+10:30, Peter T wrote: Dave, I think you will need to explain much more clearly what you want to do. I sort of got the gist but not enough to even think making a suggestion. Peter T Hello Peter, I realised this probably did not make sense so I thought I had deleted the post, so I was surprised to get a reply. .... Just as a little background to explain that... m.p.e.p is a usenet newsgroup. It may not look like anything but a typical forum on google groups, but messages are broadcast to any and all subscribed news servers, not just retrieved from a central location. While there is a "delete" protocol, virtually no nntp (network news transport protocol) servers implement it. Hence, the thread is out there and will be forever whether you can delete it from the local server/group display server you use or not...  
#6




Worksheet_BeforeDoubleClick subtract sum of cells below top cell
On Monday, January 4, 2021 at 1:59:21 AM UTC+10:30, dpb wrote:
On 1/2/2021 7:53 AM, Dave wrote: On Friday, January 1, 2021 at 1:29:22 AM UTC+10:30, Peter T wrote: Dave, I think you will need to explain much more clearly what you want to do.. I sort of got the gist but not enough to even think making a suggestion. Peter T Hello Peter, I realised this probably did not make sense so I thought I had deleted the post, so I was surprised to get a reply. ... Just as a little background to explain that... m.p.e.p is a usenet newsgroup. It may not look like anything but a typical forum on google groups, but messages are broadcast to any and all subscribed news servers, not just retrieved from a central location. While there is a "delete" protocol, virtually no nntp (network news transport protocol) servers implement it. Hence, the thread is out there and will be forever whether you can delete it from the local server/group display server you use or not...  Hello Peter, I really appreciate your reply and yes I was being a bit vague and just realised I stuffed up with my end result calculation (sorry for any confusion).. If you do a web search on 'First' cell or 'Top' cell there are many, many results without being specific to what I was asking and the terms seemed to generate quite a bit of confusion. I was deliberately trying to avoid using cell references as I have noted on various other forums that even though someone asks for a dynamic or non specific range, as soon as they quote cell references the answers come back with specific column and row references. So here is my question in term of cell references: D3 = 5,180 D4 = 1,220 D5 = (1,380 / 2) D6 = (500 + 500) D7 = 2,270 So if you double click in cell D7 (blank cell) the macro would insert a formula i.e. D3  D4  D5  D6 OR D3  SUM(D46) I was hoping the result would be a formula and not a value, so I can select it in the formula bar and check or manually edit which cells were used in the formula calculations. Thank you for your help. Regards, David 
#7




Worksheet_BeforeDoubleClick subtract sum of cells below top cell
On Monday, January 4, 2021 at 9:41:57 AM UTC+10:30, Dave wrote:
On Monday, January 4, 2021 at 1:59:21 AM UTC+10:30, dpb wrote: On 1/2/2021 7:53 AM, Dave wrote: On Friday, January 1, 2021 at 1:29:22 AM UTC+10:30, Peter T wrote: Dave, I think you will need to explain much more clearly what you want to do. I sort of got the gist but not enough to even think making a suggestion. Peter T Hello Peter, I realised this probably did not make sense so I thought I had deleted the post, so I was surprised to get a reply. ... Just as a little background to explain that... m.p.e.p is a usenet newsgroup. It may not look like anything but a typical forum on google groups, but messages are broadcast to any and all subscribed news servers, not just retrieved from a central location.. While there is a "delete" protocol, virtually no nntp (network news transport protocol) servers implement it. Hence, the thread is out there and will be forever whether you can delete it from the local server/group display server you use or not...  Hello Peter, I really appreciate your reply and yes I was being a bit vague and just realised I stuffed up with my end result calculation (sorry for any confusion). If you do a web search on 'First' cell or 'Top' cell there are many, many results without being specific to what I was asking and the terms seemed to generate quite a bit of confusion. I was deliberately trying to avoid using cell references as I have noted on various other forums that even though someone asks for a dynamic or non specific range, as soon as they quote cell references the answers come back with specific column and row references. So here is my question in term of cell references: D3 = 5,180 D4 = 1,220 D5 = (1,380 / 2) D6 = (500 + 500) D7 = 2,270 So if you double click in cell D7 (blank cell) the macro would insert a formula i.e. D3  D4  D5  D6 OR D3  SUM(D46). Where cell D3 is what I am referring to as being the 'First' cell or 'Top' cell. I was hoping the result would be a formula and not a value, so I can select it in the formula bar and check or manually edit which cells were used in the formula calculations. Thank you for your help. Regards, David 
#8




Worksheet_BeforeDoubleClick subtract sum of cells below top cell
Hello Peter,
I really appreciate your reply and yes I was being a bit vague and just realised I stuffed up with my end result calculation (sorry for any confusion). If you do a web search on 'First' cell or 'Top' cell there are many, many results without being specific to what I was asking and the terms seemed to generate quite a bit of confusion. I was deliberately trying to avoid using cell references as I have noted on various other forums that even though someone asks for a dynamic or non specific range, as soon as they quote cell references the answers come back with specific column and row references. So here is my question in term of cell references: D3 = 5,180 D4 = 1,220 D5 = (1,380 / 2) D6 = (500 + 500) D7 = 2,270 So if you double click in cell D7 (blank cell) the macro would insert a formula i.e. D3  D4  D5  D6 OR D3  SUM(D46) I was hoping the result would be a formula and not a value, so I can select it in the formula bar and check or manually edit which cells were used in the formula calculations. Thank you for your help. Regards, David ============================= Hi David, I take it you didn't see the example I posted yesterday...? From your detail above I see I misunderstood what defines the 'top cell' and in turn the sum range, so try this instead  ' in the Worksheet module Option Explicit Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) If Target.Count 1 Then ' multiple cells selected ElseIf IsEmpty(Target(0, 1)) Then ' the cell above is empty ElseIf IsEmpty(Target(1, 1)) Then ' need at least two cells above the formula cell (or at least 3?) ElseIf IsEmpty(Target) Or Target.HasFormula Then ' the cell is empty or has a formula (to be amended) Cancel = True AddFormula Target End If End Sub Sub AddFormula(rFormulaCell As Range) Dim lSumRows As Long, lTopCellRow As Long Dim sSumAddr As String, sTopCellAddr As String Dim sFml As String With rFormulaCell ' first row of the contiguous cells above lTopCellRow = .Cells(0, 1).End(xlUp).Row ' top cell address sTopCellAddr = .Offset(lTopCellRow  .Row, 0).Address(False, False) ' count rows to sum = formulaCellRow  topCellRow  1 (exclude the top cell) lSumRows = .Row  lTopCellRow  1 ' the sum address sSumAddr = .Offset(lSumRows).Resize(lSumRows).Address(False, False) ' create the formula sFml = "=" & sTopCellAddr & "  Sum(" & sSumAddr & ")" .Formula = sFml ' apply the formula .Font.Color = vbBlue ' optional font colour End With End Sub Peter T 
#9




Worksheet_BeforeDoubleClick subtract sum of cells below top cell
On Monday, January 4, 2021 at 9:23:25 PM UTC+10:30, Peter T wrote:
Hello Peter, I really appreciate your reply and yes I was being a bit vague and just realised I stuffed up with my end result calculation (sorry for any confusion). If you do a web search on 'First' cell or 'Top' cell there are many, many results without being specific to what I was asking and the terms seemed to generate quite a bit of confusion. I was deliberately trying to avoid using cell references as I have noted on various other forums that even though someone asks for a dynamic or non specific range, as soon as they quote cell references the answers come back with specific column and row references. So here is my question in term of cell references: D3 = 5,180 D4 = 1,220 D5 = (1,380 / 2) D6 = (500 + 500) D7 = 2,270 So if you double click in cell D7 (blank cell) the macro would insert a formula i.e. D3  D4  D5  D6 OR D3  SUM(D46) I was hoping the result would be a formula and not a value, so I can select it in the formula bar and check or manually edit which cells were used in the formula calculations. Thank you for your help. Regards, David ============================= Hi David, I take it you didn't see the example I posted yesterday...? From your detail above I see I misunderstood what defines the 'top cell' and in turn the sum range, so try this instead  ' in the Worksheet module Option Explicit Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) If Target.Count 1 Then ' multiple cells selected ElseIf IsEmpty(Target(0, 1)) Then ' the cell above is empty ElseIf IsEmpty(Target(1, 1)) Then ' need at least two cells above the formula cell (or at least 3?) ElseIf IsEmpty(Target) Or Target.HasFormula Then ' the cell is empty or has a formula (to be amended) Cancel = True AddFormula Target End If End Sub Sub AddFormula(rFormulaCell As Range) Dim lSumRows As Long, lTopCellRow As Long Dim sSumAddr As String, sTopCellAddr As String Dim sFml As String With rFormulaCell ' first row of the contiguous cells above lTopCellRow = .Cells(0, 1).End(xlUp).Row ' top cell address sTopCellAddr = .Offset(lTopCellRow  .Row, 0).Address(False, False) ' count rows to sum = formulaCellRow  topCellRow  1 (exclude the top cell) lSumRows = .Row  lTopCellRow  1 ' the sum address sSumAddr = .Offset(lSumRows).Resize(lSumRows).Address(False, False) ' create the formula sFml = "=" & sTopCellAddr & "  Sum(" & sSumAddr & ")" .Formula = sFml ' apply the formula .Font.Color = vbBlue ' optional font colour End With End Sub Peter T Hello Peter, Firstly.... wow... I cannot thank you enough for persisting with me. Your solution is fantastic and the optional font colour was a nice touch. Yes I did see your previous code and for some reason I could not get it to work. I created several contiguous ranges in several columns and right clicked above, below and to the left and right and nothing happened. It was only when I right clicked in row 1 that something happened and that was a 'Runtime error '1004:'' I really appreciate all your help and if there is an option, I would mark this as solved. Regards, David 
#10




Worksheet_BeforeDoubleClick subtract sum of cells below top cell
"Dave" wrote in message
[snip] Hello Peter, Firstly.... wow... I cannot thank you enough for persisting with me. Your solution is fantastic and the optional font colour was a nice touch. Yes I did see your previous code and for some reason I could not get it to work. I created several contiguous ranges in several columns and right clicked above, below and to the left and right and nothing happened. It was only when I right clicked in row 1 that something happened and that was a 'Runtime error '1004:'' I really appreciate all your help and if there is an option, I would mark this as solved. Regards, David Glad it's working and don't worry about marking. Feedback is always appreciated but in my no doubt minority view the 'marking & points system', which is now the norm in forums like this, is counter productive! Peter T 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
subtract a single cell from range of cells and then count  Excel Worksheet Functions  
Quick ?  Worksheet_BeforeDoubleClick  Excel Programming  
Worksheet_BeforeDoubleClick()  Excel Programming  
Worksheet_beforedoubleclick  Excel Programming  
Sub Worksheet_BeforeDoubleClick from elsewhere?  Excel Discussion (Misc queries) 