Hi
try:
Sub insert_formula()
Dim lastrow As Long
Dim sformula
Application.ScreenUpdating = False
sformula = "=R[0]C[-4]*R[0]C[-1]"
With ActiveSheet
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("E2:E" & lastrow).FormulaR1C1 = sformula
.Range("E" & lastrow + 1).FormulaR1C1 = "=sum(R2C
[0]:R" & lastrow & "C[0])"
End With
Application.ScreenUpdating = True
End Sub

-----Original Message-----
Would anyone be able to help me write a macro which takes
the following
table of plain numbers and inserts a formula into the
last cell which
multiplies the quantity by the unit and copies it all the
way down.

And then replace the plain total value at the bottom into
a sum cell
totalling all the subtotals above.

The amount of data varies from one text import to the
next as well. In
this example it is 3 lines, but it could in practice be
any number of
lines of data and

Qty Code Description Unit SubTotal
2, CD1,Desc001,10,20
3,CD2,DESC003,30,90
4,CD3,DESC004,40,160
,,,Grand Total,270

Thanks in hope.

TW


---
Message posted from http://www.ExcelForum.com/

.