I have this to start

Sub Total()
Range("D1").Value = Range("E1").Value + Range("F1").Value

What do I have to add to this so it continues down 500 rows (D500 = E500
+D500)

Thanks in advance!

Sub makeformula()
For i = 1 To 500
Cells(i, "d").Value = Cells(i, "e") + Cells(i, "f")
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kristen" wrote in message
...
I have this to start

Sub Total()
Range("D1").Value = Range("E1").Value + Range("F1").Value

What do I have to add to this so it continues down 500 rows (D500 = E500
+D500)

Thanks in advance!

Copy this to your public code module1 in the VBE. It will add columns.E and
F for each row that has a value in column E of the active sheet.


Sub Total()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 5).End(xlUp).Row
Set rng = sh.Range("D1:D" & lr)
For Each c In rng
c = c.Offset(0, 1) + c.Offset(0, 2)
Next
End Sub

"Kristen" wrote in message
...
I have this to start

Sub Total()
Range("D1").Value = Range("E1").Value + Range("F1").Value

What do I have to add to this so it continues down 500 rows (D500 = E500
+D500)

Thanks in advance!

It seems that one option would be to put the formula = E1 + F1 in cell D1.
But maybe that is not what you want in which case you could use this routine:

Sub testing()

Dim lng As Long

For lng = 1 To 500
Range("D" & lng).Value = Range("E" & lng).Value _
+ Range("F" & lng).Value
Next lng

End Sub

Tom (is my name not part of code)

Hi Tom,
Thanks for answering my question. I get a "Compile Error Invalid outside
procedure" error when I tried this. Yes, the formula method would work,
however, I also need to delete the contents of columns E and F while keeping
the added values.

"tompl" wrote:

It seems that one option would be to put the formula = E1 + F1 in cell D1.
But maybe that is not what you want in which case you could use this routine:

Sub testing()

Dim lng As Long

For lng = 1 To 500
Range("D" & lng).Value = Range("E" & lng).Value _
+ Range("F" & lng).Value
Next lng

End Sub

Tom (is my name not part of code)

Sub makeformula()
For i = 2 To 22
Cells(i, "d").Value = Cells(i, "e") + Cells(i, "f")
Next i
Columns("e:f").clearcontents 'Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kristen" wrote in message
...
Hi Tom,
Thanks for answering my question. I get a "Compile Error Invalid outside
procedure" error when I tried this. Yes, the formula method would work,
however, I also need to delete the contents of columns E and F while
keeping
the added values.

"tompl" wrote:

It seems that one option would be to put the formula = E1 + F1 in cell
D1.
But maybe that is not what you want in which case you could use this
routine:

Sub testing()

Dim lng As Long

For lng = 1 To 500
Range("D" & lng).Value = Range("E" & lng).Value _
+ Range("F" & lng).Value
Next lng

End Sub

Tom (is my name not part of code)

I don't know why you get the compile error, maybe it is a 2007 issue as I run
excel 2003. I have added the code to clear the contents of columns D & F
here, but you might want to try Don Guillett's code.


Sub testing()

Dim lng As Long

For lng = 1 To 100
Range("D" & lng).Value = Range("E" & lng).Value + Range("F" & lng).Value
Range("E" & lng, "F" & lng).ClearContents
Next lng

End Sub