Would anyone be able to help me write a macro which takes the followin
table of plain numbers and inserts a formula into the last cell whic
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 cel
totalling all the subtotals above.

The amount of data varies from one text import to the next as well. I
this example it is 3 lines, but it could in practice be any number o
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.

T

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

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/

.

Thanks Frank for your help as always.

However the editor doesn't like the following line which is coming u
as highlighted in red ?

Any ideas on whats happening here ?

Thank

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

Probably a word wrap problem. Try this:

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

--
Regards,
Tom Ogilvy


"twaccess " wrote in message
...
Thanks Frank for your help as always.

However the editor doesn't like the following line which is coming up
as highlighted in red ?

Any ideas on whats happening here ?

Thanks


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

Sorry this is the text that is coming up in Red

[0]:R" & lastrow & "C[0])"

Thank

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

Answered.

--
Regards,
Tom Ogilvy

"twaccess " wrote in message
...
Sorry this is the text that is coming up in Red

[0]:R" & lastrow & "C[0])"

Thanks


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

Hi Tom
thanks for stepping in :-)

Regards
Frank

-----Original Message-----
Answered.

--
Regards,
Tom Ogilvy

"twaccess "
wrote in message
...
Sorry this is the text that is coming up in Red

[0]:R" & lastrow & "C[0])"

Thanks


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



.

Thanks guys

It was a text wrapping issue. I should have checked this first !

Regards


Terr

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