'Find' code question

[sitewolf]

I have files that I update every month. I created pivot tables to accomplish what is needed, but some users insist on still seeing the information the way they'd always done before I came along......hundreds of lines of general ledger coding split into groups by various criteria.

I've also been steering away from macros because they're disabled by our admins, however I recently realized I can put some in my personal macros workbook.

Anyway, at certain changes in accounting codes, I insert 2 blank rows- one to put in subtotals, the second for a break between groups. Then I autosum and format monthly and ytd amounts for the group. Currently, I have a macro that inserts the 2 blank rows, moves to the columns with the amounts, and does the autosum/formatting. My 'problem' is that the amounts in some files are 1 column different than other files- monthly and ytd is in columns J and K in some, in K and L in others.

So, while I can just have 2 versions of the macro, the ideal would be 1...and that brings me here. The one constant in all these lines in all these files is that a 4 digit year is always in the last (rightmost) column with the monthly and ytd amounts always 3 and 2 columns to the left. Therefore, if I can 'find' the 4 digit year in the row above the newly inserted blank rows, then move down a row, left 3 and 2 columns, THEN autosum/format, I'd only need the one version.

Clear as mud?
Example of a line:
[TABLE="width: 578"]
<tbody>[TR]
[TD]1000[/TD]
[TD="align: right"]5200000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1900000[/TD]
[TD] J02 [/TD]
[TD] 08 [/TD]
[TD] 228.96 [/TD]
[TD] 610.28 [/TD]
[TD]04[/TD]
[TD]2017[/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col span="2"><col><col><col><col span="2"><col><col></colgroup>[/TABLE]

 

[cooper645]

If they all have constant headers you could use something like what was recently suggested in answer to another question here

 

[mikerickson]

If the year is in the last column, you could use Cells(rowNumber, Columns.Count).End(xlToLeft) to find the last cell in row rowNumber.
Then use .Offset(0, -2) and .Offset(0, -3) to find the proper column for the ytd and monthly values.