Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 spacings 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(D3:D5) = 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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
"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 sum-range cell Set rCellTmp = Cells(lFirstSumRow, .Column) lTopCellRow = rCellTmp.End(xlUp).Row If lTopCellRow 1 Then ' assume the topcell must be below row-1 ? 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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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(D4:D6) 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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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(D4:D6). 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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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(D4:D6) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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) |