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

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

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

"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

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...

--

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

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

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

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(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
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 'Run-time error '1004:''

I really appreciate all your help and if there is an option, I would mark this as solved.

Regards, David

"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 'Run-time 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