"SUM" by headings

[Panoos64]

Hi all, kindly advise me so that to create a VBA code which should “SUM” the headings and rows down in columns “SALARY” & “ACTUAL SALARY”. Therefore, that the above headings have no stably column, but the row “2” is constantly. Below I present the original data and the expected result. Thanking you in advance

Original data

<tbody>
[TD="class: xl65"][/TD]
[TD="class: xl66, width: 154"]P[/TD]
[TD="class: xl66, width: 118"]Q[/TD]
[TD="class: xl66, width: 60"]R[/TD]
[TD="class: xl66, width: 206"]S[/TD]
[TD="class: xl66, width: 100"]T[/TD]

[TD="class: xl67, width: 29"]1[/TD]
[TD="class: xl70, width: 154"]COMPANY[/TD]
[TD="class: xl68, width: 118"][/TD]
[TD="class: xl68, width: 60"][/TD]
[TD="class: xl68, width: 206"][/TD]
[TD="class: xl68, width: 100"][/TD]

[TD="class: xl67, width: 29"]2[/TD]
[TD="class: xl68, width: 154"]EMPL. NAME[/TD]
[TD="class: xl72, width: 118"]EMPL. CODE[/TD]
[TD="class: xl68, width: 60"]SALARY[/TD]
[TD="class: xl68, width: 206"]ACTUAL SALARY[/TD]
[TD="class: xl68, width: 100"]DIFFERENCE[/TD]

[TD="class: xl67, width: 29"]3[/TD]
[TD="class: xl71, width: 154"]EMPLOYEE NAME1[/TD]
[TD="class: xl66, width: 118"]450010[/TD]
[TD="class: xl73, width: 60"]1,570.00[/TD]
[TD="class: xl73, width: 206"]1,480.00[/TD]
[TD="class: xl69, width: 100"][/TD]

[TD="class: xl67, width: 29"]4[/TD]
[TD="class: xl71, width: 154"]EMPLOYEE NAME2[/TD]
[TD="class: xl66, width: 118"]450146[/TD]
[TD="class: xl73, width: 60"]1,850.00[/TD]
[TD="class: xl73, width: 206"]1,588.00[/TD]
[TD="class: xl69, width: 100"][/TD]

</tbody>

Expected result

<tbody>
[TD="class: xl65"][/TD]
[TD="class: xl66, width: 154"]P[/TD]
[TD="class: xl66, width: 118"]Q[/TD]
[TD="class: xl66, width: 60"]R[/TD]
[TD="class: xl66, width: 206"]S[/TD]
[TD="class: xl66, width: 100"]T[/TD]

[TD="class: xl67, width: 29"]1[/TD]
[TD="class: xl69, width: 154"]COMPANY[/TD]
[TD="class: xl68, width: 118"][/TD]
[TD="class: xl68, width: 60"][/TD]
[TD="class: xl68, width: 206"][/TD]
[TD="class: xl68, width: 100"][/TD]

[TD="class: xl67, width: 29"]2[/TD]
[TD="class: xl68, width: 154"]EMPL. NAME[/TD]
[TD="class: xl71, width: 118"]EMPL. CODE[/TD]
[TD="class: xl68, width: 60"]SALARY[/TD]
[TD="class: xl68, width: 206"]ACTUAL SALARY[/TD]
[TD="class: xl68, width: 100"]DIFFERENCE[/TD]

[TD="class: xl67, width: 29"]3[/TD]
[TD="class: xl70, width: 154"]EMPLOYEE NAME1[/TD]
[TD="class: xl66, width: 118"]450010[/TD]
[TD="class: xl72, width: 60"]1,570.00[/TD]
[TD="class: xl72, width: 206"]1,480.00[/TD]
[TD="class: xl73, width: 100"]90.00[/TD]

[TD="class: xl67, width: 29"]4[/TD]
[TD="class: xl70, width: 154"]EMPLOYEE NAME2[/TD]
[TD="class: xl66, width: 118"]450146[/TD]
[TD="class: xl72, width: 60"]1,850.00[/TD]
[TD="class: xl72, width: 206"]1,588.00[/TD]
[TD="class: xl73, width: 100"]262.00[/TD]

</tbody>

 

[oldbrewer]

=if(q3<>"",r3-s3,"")

 

[Peter_SSs]

See if this does what you want.

Sub Insert_Formula()
  Dim rSalary As Range, rActualSalary As Range, rDifference As Range
  
  Set rSalary = Rows(2).Find(What:="salary", LookAt:=xlWhole, MatchCase:=False)
  Set rActualSalary = Rows(2).Find(What:="actual salary", LookAt:=xlWhole, MatchCase:=False)
  Set rDifference = Rows(2).Find(What:="difference", LookAt:=xlWhole, MatchCase:=False)
  If Not rSalary Is Nothing And Not rActualSalary Is Nothing And Not rDifference Is Nothing Then
    With rDifference.Offset(1).Resize(Cells(Rows.Count, rSalary.Column).End(xlUp).Row - 2)
      .FormulaR1C1 = "=RC[" & rSalary.Column - .Column & "]-RC[" & rActualSalary.Column - .Column & "]"
    End With
  End If
End Sub

 

[Panoos64]

Thank you so much Peter! Is perfect work. Many thanks also for your continued support. Hv a great, lovely day!

 

[Peter_SSs]

Thank you so much Peter! Is perfect work. Many thanks also for your continued support. Hv a great, lovely day!

You're welcome. Thanks for the kind words.