Calculated value based on current line selection
 

I have just about refined my entire project. I'm down to
this part here. Again, this code works. However, I want it
to be as well written as it can be.

Notice the lines below that start with:
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Formula = sformula
Selection.Copy
Selection.PasteSpecial xlPasteValues

This top line goes to a blank line. I found so far that I
had to select it because of the fact that I have a
spreadsheet range referenced in sformula below that
depends on the value of the range lines1, and its formula
is: "=CELL("ROW")-2". So, this means if the selection in
this case in on row 4080, then the value of lines1 in that
case is 4078. That value is a part of the result of the
formula sformula. So, is there any other way to pass that
result to sformula if the active cell isn't there?

After the formula result is done, then it has to paste its
value in the active cell, which is what the last two lines
above are for. Finally it passes the value of lines1 over
to the range lines 2, so that on the 2nd and subsequent
passes, the current row selection less the 4078 gets
caculated in formula sformula. So, next time, it ends up
being only 80.

The complete with section is reprinted below if you want
to see how that portion fits together.




With Sheets("Data_Assembly")
.Range("Workarea").Resize(8).Value = Range _
("Sum_it").Resize(8).Value
.Range("Workarea").CurrentRegion.Copy
End With
With Sheets("Report")
.Range("A1").End(xlDown).Offset(1, 0).PasteSpecial _
xlPasteValues

sformula = "=IF(FIXED(INDEX(Summary,ROWSUM+1,2),0,TRUE)
=INDEX(Summary,ROWSUM+1,1)," & _
"""SE~""&FIXED(LINES1,0,TRUE)&""~0001""," & _
"""SE~""&FIXED(LINES1-LINES2,0)&""~0001"")"
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Formula = sformula
Selection.Copy
Selection.PasteSpecial xlPasteValues
Range("Lines2") = Range("lines1")
End With