purple_ninja
03-04-2016, 02:32 AM
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
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