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