Hello everyone.
I would really appreciate if someone would be kind enough to help me out as I am still trying to learn VBA. I have a basic spreadsheet (attached) with 3 years of data for 2 companies. The Years do not necessarily correspond for each company. As a new company is started the User can add additional columns. All data is totalled by Year. I am trying to run a macro that will compare the year headings (for each new company) to the year headings for the Totals. If the Year is not already there then add it to the end. I will then copy the formula to the new columns. Many thanks in advance. [TABLE="border: 1, cellpadding: 1, width: 500"]
Coy A
[/td]Coy A
[/td]Coy A
[/td]Coy B
[/td]Coy B
[/td]Coy B
[/td]Total
[/td]Total
[/td]Total
[/td]Total
[/td]Total
[/td]2017
[/td]2018
[/td]2019
[/td]2019
[/td]2020
[/td]2021
[/td]2017
[/td]2018
[/td]2019
[/td]20
[/td]40
[/td]30
[/td]10
[/td]20
[/td]25
[/td]20
[/td]40
[/td]30
[/td]Coy A
[/td]Coy A
[/td]Coy A
[/td]Coy B
[/td]Coy B
[/td]Coy B
[/td]Total
[/td]Total
[/td]Total
[/td]Total
[/td]Total
[/td]Total
[/td]2017
[/td]2018
[/td]2019
[/td]2019
[/td]2020
[/td]2021
[/td]2017
[/td]2018
[/td]2019
[/td]2020
[/td]2021
[/td]20
[/td]40
[/td]30
[/td]10
[/td]20
[/td]25
[/td]20
[/td]40
[/td]30
[/td]20
[/td]25
[/td]
[/TABLE]
[ATTACH]n1201569[/ATTACH]
This is my code so far, but is doing too many loops! and I am not sure why. I have tried several bits of code from the web but not getting the correct result.
[VBA]Sub Test2()
Dim Stg2Cell As Range
Dim TotalCell As Range
Dim Stg2Title As Range
Dim TotalTitle As Range
Set Stg2Title = Range("e2:g2")
Set TotalTitle = Range("H2:j2")
For Each Stg2Cell In Stg2Title
For Each TotalCell In TotalTitle
If Stg2Cell > TotalCell Then
lastcol = Sheet1.Cells(2, Application.Columns.Count).End(xlToLeft).Column
Cells(2, lastcol + 1).Value = Stg2Cell.Value
End If
Next
Next
End Sub
[/VBA]