Sum the values between two specified column headings - number of columns between will change

M

Moley84

New Member
Joined
Jul 11, 2024
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Good morning all,

My column headings are in row 7. The below code works fine. However, the ActiveCell.Formula = "=G8" needs changing (the active cell is the Subtotal column). I need it to sum the values between two columns called "Calculated payment based on time and costs" and "Subtotal" and then multiple that value by what's in column G. For example, as per screenshot, =(SUM(H18:K18))*G18. If these columns were static, I would be fine but the number of columns between these columns will change, meaning that the K18 part of the formula above would need to change. The "Calculated payment based on time and costs" will always be in column G but the "Subtotal" column will change. I'm guessing I need to use Find(What:= which is already in this code for another reason but I don't know how to incorporate that into code with a formula being calculated as well.

Any help most gratefully received :). Thank you.

VBA Code: 
'Calculate the far right columns
'Medical
 ' Subtotal
    Dim RRMedicalFinal
    RRMedicalFinal = Sheets("PatientLevelCostsMedical").Cells(Rows.Count, "B").End(xlUp).Row

    Sheets("PatientLevelCostsMedical").Activate
    Sheets("PatientLevelCostsMedical").Rows(7).Find(What:="Subtotal", LookAt:=xlWhole).Activate
    ActiveCell.Offset(1).Activate
    ActiveCell.Formula = "=G8"
    ActiveCell.NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""_-;_-@_-"
    If RRMedicalFinal = 8 Then GoTo AfterAutoFillNurse1
    ActiveCell.AutoFill Destination:=Sheets("PatientLevelCostsMedical").Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(RRMedicalFinal, ActiveCell.Column))
    Sheets("PatientLevelCostsMedical").Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(RRMedicalFinal, ActiveCell.Column)).NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""_-;_-@_-"
AfterAutoFillMedical1:
1729670347558.png
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hello,

If the cell where the formula is written is always 1 column to the right to the last column, you could use offset?

Excel Formula: 
=SUM(H18:OFFSET(INDIRECT(ADDRESS(ROW(); COLUMN()));0;-1))

Otherwise in VBA you can use

VBA Code: 
Range("L18").Formula = "=SUM(" & Range("H18").Address(False, False) & ":" & Range("L18").Offset(0;-1).Address(False, False) & "))*" & Range("G18").Address(False, False)
 
Last edited:
Upvote 0
saboh12617 said:
Hello,

If the cell where the formula is written is always 1 column to the right to the last column, you could use offset?

Excel Formula: 
=SUM(H18:OFFSET(INDIRECT(ADDRESS(ROW(); COLUMN()));0;-1))

Otherwise in VBA you can use

VBA Code: 
Range("L18").Formula = "=SUM(" & Range("H18").Address(False, False) & ":" & Range("L18").Offset(0;-1).Address(False, False) & "))*" & Range("G18").Address(False, False)
Click to expand...
Thank you very much for your reply Saboh12617. When I hover over the ActiveCell.Formula in debug mode, it shows the correct value. However, I'm getting a Run-time error '1004' Application-defined or object-defined error. Any ideas?
 
Upvote 0
Moley84 said:
Good morning all,

My column headings are in row 7. The below code works fine. However, the ActiveCell.Formula = "=G8" needs changing (the active cell is the Subtotal column). I need it to sum the values between two columns called "Calculated payment based on time and costs" and "Subtotal" and then multiple that value by what's in column G. For example, as per screenshot, =(SUM(H18:K18))*G18. If these columns were static, I would be fine but the number of columns between these columns will change, meaning that the K18 part of the formula above would need to change. The "Calculated payment based on time and costs" will always be in column G but the "Subtotal" column will change. I'm guessing I need to use Find(What:= which is already in this code for another reason but I don't know how to incorporate that into code with a formula being calculated as well.

Any help most gratefully received :). Thank you.

VBA Code: 
'Calculate the far right columns
'Medical
 ' Subtotal
    Dim RRMedicalFinal
    RRMedicalFinal = Sheets("PatientLevelCostsMedical").Cells(Rows.Count, "B").End(xlUp).Row

    Sheets("PatientLevelCostsMedical").Activate
    Sheets("PatientLevelCostsMedical").Rows(7).Find(What:="Subtotal", LookAt:=xlWhole).Activate
    ActiveCell.Offset(1).Activate
    ActiveCell.Formula = "=G8"
    ActiveCell.NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""_-;_-@_-"
    If RRMedicalFinal = 8 Then GoTo AfterAutoFillNurse1
    ActiveCell.AutoFill Destination:=Sheets("PatientLevelCostsMedical").Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(RRMedicalFinal, ActiveCell.Column))
    Sheets("PatientLevelCostsMedical").Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(RRMedicalFinal, ActiveCell.Column)).NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""_-;_-@_-"
AfterAutoFillMedical1:
View attachment 118410
Click to expand...
Moley84 said:
Good morning all,

My column headings are in row 7. The below code works fine. However, the ActiveCell.Formula = "=G8" needs changing (the active cell is the Subtotal column). I need it to sum the values between two columns called "Calculated payment based on time and costs" and "Subtotal" and then multiple that value by what's in column G. For example, as per screenshot, =(SUM(H18:K18))*G18. If these columns were static, I would be fine but the number of columns between these columns will change, meaning that the K18 part of the formula above would need to change. The "Calculated payment based on time and costs" will always be in column G but the "Subtotal" column will change. I'm guessing I need to use Find(What:= which is already in this code for another reason but I don't know how to incorporate that into code with a formula being calculated as well.

Any help most gratefully received :). Thank you.

VBA Code: 
'Calculate the far right columns
'Medical
 ' Subtotal
    Dim RRMedicalFinal
    RRMedicalFinal = Sheets("PatientLevelCostsMedical").Cells(Rows.Count, "B").End(xlUp).Row

    Sheets("PatientLevelCostsMedical").Activate
    Sheets("PatientLevelCostsMedical").Rows(7).Find(What:="Subtotal", LookAt:=xlWhole).Activate
    ActiveCell.Offset(1).Activate
    ActiveCell.Formula = "=G8"
    ActiveCell.NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""_-;_-@_-"
    If RRMedicalFinal = 8 Then GoTo AfterAutoFillNurse1
    ActiveCell.AutoFill Destination:=Sheets("PatientLevelCostsMedical").Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(RRMedicalFinal, ActiveCell.Column))
    Sheets("PatientLevelCostsMedical").Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(RRMedicalFinal, ActiveCell.Column)).NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""_-;_-@_-"
AfterAutoFillMedical1:
View attachment 118410
Click to expand...

Moley84 said:
Good morning all,

My column headings are in row 7. The below code works fine. However, the ActiveCell.Formula = "=G8" needs changing (the active cell is the Subtotal column). I need it to sum the values between two columns called "Calculated payment based on time and costs" and "Subtotal" and then multiple that value by what's in column G. For example, as per screenshot, =(SUM(H18:K18))*G18. If these columns were static, I would be fine but the number of columns between these columns will change, meaning that the K18 part of the formula above would need to change. The "Calculated payment based on time and costs" will always be in column G but the "Subtotal" column will change. I'm guessing I need to use Find(What:= which is already in this code for another reason but I don't know how to incorporate that into code with a formula being calculated as well.

Any help most gratefully received :). Thank you.

VBA Code: 
'Calculate the far right columns
'Medical
 ' Subtotal
    Dim RRMedicalFinal
    RRMedicalFinal = Sheets("PatientLevelCostsMedical").Cells(Rows.Count, "B").End(xlUp).Row

    Sheets("PatientLevelCostsMedical").Activate
    Sheets("PatientLevelCostsMedical").Rows(7).Find(What:="Subtotal", LookAt:=xlWhole).Activate
    ActiveCell.Offset(1).Activate
    ActiveCell.Formula = "=G8"
    ActiveCell.NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""_-;_-@_-"
    If RRMedicalFinal = 8 Then GoTo AfterAutoFillNurse1
    ActiveCell.AutoFill Destination:=Sheets("PatientLevelCostsMedical").Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(RRMedicalFinal, ActiveCell.Column))
    Sheets("PatientLevelCostsMedical").Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(RRMedicalFinal, ActiveCell.Column)).NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""_-;_-@_-"
AfterAutoFillMedical1:
View attachment 118410
Click to expand...
Give this a go. It avoids using volatile functions.

Sum the values between two specified column headings.xlsm
ABCDEFGHIJKLM
1
2
3
4
5
6
7Calculated payment based on time and costsBaselineVisit 1Visit 2 (subset)Follow-up InformationSubtotal
8£938.001£938.00
9£930.001£930.00
10£108.001£108.00
11£456.00111£1,368.00
12£516.001£516.00
13£539.001£539.00
14
15
Sheet1 (2)
Cell Formulas
RangeFormula
L8:L13L8=IFERROR(SUM($H8:INDEX(8:8,1,MATCH("Subtotal",$7:$7,0)-1))*G8,"")
 
Upvote 0
HighAndWilder said:
Give this a go. It avoids using volatile functions.

Sum the values between two specified column headings.xlsm
ABCDEFGHIJKLM
1
2
3
4
5
6
7Calculated payment based on time and costsBaselineVisit 1Visit 2 (subset)Follow-up InformationSubtotal
8£938.001£938.00
9£930.001£930.00
10£108.001£108.00
11£456.00111£1,368.00
12£516.001£516.00
13£539.001£539.00
14
15
Sheet1 (2)
Cell Formulas
RangeFormula
L8:L13L8=IFERROR(SUM($H8:INDEX(8:8,1,MATCH("Subtotal",$7:$7,0)-1))*G8,"")
Click to expand...
Thank you High and Wilder. I am getting the Run-time error '1004' Application-defined or object-defined error with this, also. I'm presuming there is something wrong with my formats? G8 is calculated by a formula: =SUM(F8*RateCard!Consultant_PerMinute) and is a 'custom' format. Subtotal has the Accountancy format. Is that what is causing this? Within the vba code for both columns, I have set the number format to be identical, ie .....NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""_-;_-@_-"
 
Upvote 0
Hello again,

On the formula approach i would definetely go with a structure similar to the proposition of HighAndWilder. Indeed volatility can be an issue if your table becomes quite big.

However for the VBA formula i was a bit confused and found the issue : in the offset i had written a ";" instead of ",". Moreover there were 2 closing parenthesis for 1 needed, hence the error.

The formula below should work

VBA Code: 
Range("L18").Formula = "=SUM(" & Range("H18").Address(False, False) & ":" & Range("L18").Offset(0, -1).Address(False, False) & ")*" & Range("G18").Address(False, False)
 
Upvote 0
Solution
Moley84 said:
Thank you High and Wilder. I am getting the Run-time error '1004' Application-defined or object-defined error with this, also. I'm presuming there is something wrong with my formats? G8 is calculated by a formula: =SUM(F8*RateCard!Consultant_PerMinute) and is a 'custom' format. Subtotal has the Accountancy format. Is that what is causing this? Within the vba code for both columns, I have set the number format to be identical, ie .....NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""_-;_-@_-"
Click to expand...
Can you submit a mini sheet using XL2BB as I did.
 
Upvote 0
Moley84 said:
Thank you High and Wilder. I am getting the Run-time error '1004' Application-defined or object-defined error with this, also. I'm presuming there is something wrong with my formats? G8 is calculated by a formula: =SUM(F8*RateCard!Consultant_PerMinute) and is a 'custom' format. Subtotal has the Accountancy format. Is that what is causing this? Within the vba code for both columns, I have set the number format to be identical, ie .....NumberFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* ""-""_-;_-@_-"
Click to expand...
The formatting is like a layer on top of the actual value. You can apply that yourself manually.

You can still use any formula you like in G8 and downwards. All my formula does is use the result of that formula to produce the Subtotal.
 
Upvote 0

saboh12617​


Saboh12617 and HighAndWilder, thank you, both of you for your help. The amended formula by Saboh worked. Thank you again. :)
 
Upvote 0
saboh12617 said:
Hello again,

On the formula approach i would definetely go with a structure similar to the proposition of HighAndWilder. Indeed volatility can be an issue if your table becomes quite big.

However for the VBA formula i was a bit confused and found the issue : in the offset i had written a ";" instead of ",". Moreover there were 2 closing parenthesis for 1 needed, hence the error.

The formula below should work

VBA Code: 
Range("L18").Formula = "=SUM(" & Range("H18").Address(False, False) & ":" & Range("L18").Offset(0, -1).Address(False, False) & ")*" & Range("G18").Address(False, False)
Click to expand...
Hi, sorry, me again.

I just tried this on another sheet which has a lot more columns. Therefore, Range("L18") needs to change to using something like INDEX or FIND?

I tried a combination of both yours and HighandWilder's, ie ActiveCell.Formula = "=SUM(" & Range("H8").Address(False, False) & ":" & "INDEX(8:8,1,MATCH(""Subtotal"",$7$7,0)-1))*" & Range("G8").Address(False, False) but am getting the same error again. Where have I gone wrong, please?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Identifying the difference between two data sets
Replies
1
Views
174
igold
igold
R
  • Solved
VBA - Summing a dynamic gap range between two named ranges?
2
Replies
13
Views
748
RockandGrohl
R
H
  • Question Question
Adjust an existing formula based on most recently added column and prior month end date.
Replies
1
Views
341
healeydm
H
O
  • Question Question
sum or subtract for the same cell based on match column between two sheets
2
Replies
13
Views
837
Omran Y
O
A
  • Solved
Create Named Range for each column based on column heading text.
Replies
5
Views
311
Cubist
Cubist

Forum statistics

Threads
1,223,872
Messages
6,175,104
Members
452,613
Latest member
amorehouse

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top