add cells
in [Programming]
|
From: Kristen on 25 Mar 2010 13:55 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!
From: Don Guillett on 25 Mar 2010 14:09 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 dguillett(a)gmail.com "Kristen" <Kristen(a)discussions.microsoft.com> wrote in message news:A79F9080-20FB-4603-9716-8E4CB7923C36(a)microsoft.com... >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!
From: JLGWhiz on 25 Mar 2010 14:13 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" <Kristen(a)discussions.microsoft.com> wrote in message news:A79F9080-20FB-4603-9716-8E4CB7923C36(a)microsoft.com... >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!
From: tompl on 25 Mar 2010 14:37 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)
From: Kristen on 25 Mar 2010 14:48
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) |