Find last column , enter formula to last column skip 3 lines and repeat

[purple_ninja]

Hi

I have a spreadsheet with a long convoluted formula that I am looking to insert and paste as values using a macro.

The formulas are group in rows of 3 :

First row = Blank
Second Row - Formula 1
Third Row Formula 2

I want to insert Formula1 and Formula 2 across X columns (this may grow or shrink so hoping to find the equivalent of xlDown to find the last column ). The pattern will be for each formula to be copied every 3rd row to the end of the spreadsheet (both with different starting points (see table below

So far I've got the following code but it's not happy :(


Sub LastColumnWithData_xlDown()
Application.ScreenUpdating = False
'Update Columns AB:AM by inserting formulas and then pasting the result
Dim lastColumn As Integer
With Worksheets("Combined").Range("R2")
lastColumn = ActiveSheet.Range("R1C1").End(xlToRight).Column
With .Resize(lastColumn - .Column + 1)
.Formula = "=IF(AND(RC11>=R4C,RC11<R4C[1]),R5C11,IF(R3C=""XSD"",""Xmas"",IF(AND(RC13>=R4C,RC13<R4C[1]),R5C13,IF(AND(RC15>=R4C,RC15<R4C[1]),R5C15,IF(AND(RC16>=R4C,RC16<R4C[1]),R5C16,IF(AND(RC19>=R4C,RC19<R4C[1]),R5C19,IF(OR(RC[1]=R5C11,RC[1]=R5C13,RC[1]=R5C14,RC[1]=R5C15,RC[1]=R5C16),SUM(MAX(RC[1]:RC[7])+11),IF(AND(RC[1]>0,RC[1]<100),SUM(MAX(RC[1]:RC[7])+11),IF(RC[1]=""Spec Comp" & _
"IF(RC[1]="""","""",IF(AND(RC[1]=""Xmas"",RC[2]=""Xmas"",OR(RC[3]="""",RC[3]=""Spec Comp"")),"""",SUM(MAX(RC[1]:RC[7])+11))))))))))))"
.Value = .Value
End With
End With

End Sub


Any help plus an explanation of how it works would be most appreciated.




Date1
Date2
Date3
Date4
Date5
Date6
Date7



DATAA










DATAA
Formula1
Formula1
Formula1
Formula1
Formula1
Formula1
Formula1



DATAA
Formula2
Formula2
Formula2
Formula2
Formula2
Formula2
Formula2



DATAB










DATAB
Formula1
Formula1
Formula1
Formula1
Formula1
Formula1
Formula1



DATAB
Formula2
Formula2
Formula2
Formula2
Formula2
Formula2
Formula2

[skywriter]

So you have dates in row1?

Using the dates in row1 we need to find the last column based on the farthest right column in row1 with a date?

Then we use the data in column A to find the last row with data?

Then we start in row3 because row1 has dates, row2 is blank?

Then we would put the formulas in rows 3&4 to the last column then 6&7 to the last column then 9&10 to the last column etc. until the last row with data in column A?

I'm asking all of this because you wrote:
First row = Blank -- But this is actually row2 of the worksheet, because of dates in row1?
Second Row - Formula 1
Third Row Formula 2

[purple_ninja]

Hi skywriter
Apologies for the lack of clarity and for the delayed response! - I meant the first row of each record

The first row of each set of records is blank due to other information to the left of my column A in the above example which for now doesn't need any formulas to the right (Each record has 3 lines of data to the left but on two on the right)

The formulas in rows 3 & 4 (which are subtly different) need to be entered 6&7, 9&10 etc to the last row of the column (which may grow or shrink).

I've attempted to do a for loop (which didn't work), XLDown and Xlright to find last row/column but I'm really out of my comfort zone - I haven't even managed to get it to copy it correctly down the column as it just typed the formula in the cell.

At the moment this spreadsheet can take up to 30 mins to run - I know if I can crack this I will reduce the processing time significantly as I've proved this with another more simple workbook.

Assistance much appreciated!

[skywriter]

You didn't answer my questions directly, which would have helped.

[purple_ninja]

So you have dates in row1?

Yes I do



Using the dates in row1 we need to find the last column based on the farthest right column in row1 with a date?

Yes that is correct



Then we use the data in column A to find the last row with data?

Yes



Then we start in row3 because row1 has dates, row2 is blank?

Yes



Then we would put the formulas in rows 3&4 to the last column then 6&7 to the last column then 9&10 to the last column etc. until the last row with data in column A?

Yes



I'm asking all of this because you wrote:
First row = Blank -- But this is actually row2 of the worksheet, because of dates in row1?
Second Row - Formula 1
Third Row Formula 2
My apologies this is what I was trying to explain in my previous response - obviously badly :( Hopefully this response is clearer?