Repetitive task macro

T

twaccess

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
 
F

Frank Kabel

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
 
T

twaccess

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
 
T

Tom Ogilvy

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
 
T

twaccess

Thanks guys

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

Regards


Terr
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create a Macro that help paste the lot# 1
Macro Runs when the user changes any cell on the work sheet 4
Divide two numbers in data subtotal function 2
Summing with Dynamic Data 2
Forms Calculations 1
Macro for Sub Totals 2
subtotal function 1
Merging duplicate lines in a Database 1

Top