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
(E-Mail Removed)
"Kristen" <(E-Mail Removed)> wrote in message
news:A79F9080-20FB-4603-9716-(E-Mail Removed)...
>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" & lr)
For Each c In rng
c = c.Offset(0, 1) + c.Offset(0, 2)
Next
End Sub

"Kristen" <(E-Mail Removed)> wrote in message
news:A79F9080-20FB-4603-9716-(E-Mail Removed)...
>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
(E-Mail Removed)
"Kristen" <(E-Mail Removed)> wrote in message
news8442729-E1AF-418D-A3AE-(E-Mail Removed)...
> 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