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

[Moley84]

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.

'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:

 

[HighAndWilder]

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?

Why are you trying to do this using VBA when a formula option is easier?

Did the mini-sheet that I posted work?

Which cell is the Active Cell when you run this code?

 

[Moley84]

Why are you trying to do this using VBA when a formula option is easier?

Did the mini-sheet that I posted work?

Which cell is the Active Cell when you run this code?

Hi, The vba code imports data and then manipulates it to different sheets. It needs to then recalculate the Subtotal field as the data has been split across sheets. That's why it's in vba. This is for a team to use, so I can't expect them to enter formula into sheets.

 

[Moley84]

Why are you trying to do this using VBA when a formula option is easier?

Did the mini-sheet that I posted work?

Which cell is the Active Cell when you run this code?

Hi, sorry, didn't answer your questions. The active cell is the cell beneath the word Subtotal.

I could see that your spreadsheet worked. I just need to add the formula within vba script.

 

[Alex Blakenburg]

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?

You are missing the colon in $7:$7.

 

[Moley84]

You are missing the colon in $7:$7.

How could I have missed that? Thank you very much. Now working.!!