I just went through a tedious exercise. I discovered some formulas whose
row references were off by two (may change). I had to manually navigate
through 100 of them over nearly 1900 rows and change them. I would like to
automate this if at some future time I make a similar discovery.

I will try to explain my data setup sufficiently to get a solution.

In ColB I have a formula every 19 rows (now) that sums values from 17 rows
(now) above it starting in row 4 in ColAB like this:
=SUM(AB4:AB20)
=SUM(AB23:AB39)
=SUM(AB42:AB58)
and so on every 19 rows (now) through (now) row 1902

The starting row will always be 4, but the number of total values to be
added (now 17) may change as I add or subtract classes from a list in ColA.
Each range in ColA starts with the student's name, followed by the current
class list (now 17) followed by 'Total Class Hours' for each of now 100
students. Cell in ColB to the right of 'Total Class Hours' adds the hours
from ColAB for each student. ColAB contains totals for each weekday for
each class attended by each student.

As I added or removed classes (I have macros to do this), I expected that
the SUM() formulas would adjust (and they usually did), but for some
unknown reason at some unknown time, they didn't. I don't want to have to
redo the formulas manually again if the same occurs.

I need something like:
For i = 1902 to 4 Step - 19
Range("B"&i).Row Formula = "SUM(AB&i-18:AB&i-1)"
Next i
I could figure out the relative numbers if they change.

or maybe some looping Offset from 'Total Class Hours' for each student

--
David

For i = 1902 to 4 Step - 19
Range("B" & i).Formula = "SUM(AB" & i - 18 & ":AB" & i-1 & ")"
Next i

Put your loop controls, start and step, in variables and you are away.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"David" wrote in message
...
I just went through a tedious exercise. I discovered some formulas whose
row references were off by two (may change). I had to manually navigate
through 100 of them over nearly 1900 rows and change them. I would like to
automate this if at some future time I make a similar discovery.

I will try to explain my data setup sufficiently to get a solution.

In ColB I have a formula every 19 rows (now) that sums values from 17 rows
(now) above it starting in row 4 in ColAB like this:
=SUM(AB4:AB20)
=SUM(AB23:AB39)
=SUM(AB42:AB58)
and so on every 19 rows (now) through (now) row 1902

The starting row will always be 4, but the number of total values to be
added (now 17) may change as I add or subtract classes from a list in
ColA.
Each range in ColA starts with the student's name, followed by the current
class list (now 17) followed by 'Total Class Hours' for each of now 100
students. Cell in ColB to the right of 'Total Class Hours' adds the hours
from ColAB for each student. ColAB contains totals for each weekday for
each class attended by each student.

As I added or removed classes (I have macros to do this), I expected that
the SUM() formulas would adjust (and they usually did), but for some
unknown reason at some unknown time, they didn't. I don't want to have to
redo the formulas manually again if the same occurs.

I need something like:
For i = 1902 to 4 Step - 19
Range("B"&i).Row Formula = "SUM(AB&i-18:AB&i-1)"
Next i
I could figure out the relative numbers if they change.

or maybe some looping Offset from 'Total Class Hours' for each student

--
David

Formula row is the row where the formulas are first entered.


Sub AddFormulas()
Dim rw as Long, i as Long
Dim StepVal as Long
rw = 10 ' formula row
StepVal = 19
For i = 4 To 1902 Step StepVal

Range("B" & rw).Formula = _
"=SUM(AB" & i & ":AB" & i + _
StepVal - 3 & ")"
rw = rw + 1
Next i

End Sub

Produced the formula you show.

--
Regards,
Tom Ogilvy

"David" wrote in message
...
I just went through a tedious exercise. I discovered some formulas whose
row references were off by two (may change). I had to manually navigate
through 100 of them over nearly 1900 rows and change them. I would like to
automate this if at some future time I make a similar discovery.

I will try to explain my data setup sufficiently to get a solution.

In ColB I have a formula every 19 rows (now) that sums values from 17 rows
(now) above it starting in row 4 in ColAB like this:
=SUM(AB4:AB20)
=SUM(AB23:AB39)
=SUM(AB42:AB58)
and so on every 19 rows (now) through (now) row 1902

The starting row will always be 4, but the number of total values to be
added (now 17) may change as I add or subtract classes from a list in
ColA.
Each range in ColA starts with the student's name, followed by the current
class list (now 17) followed by 'Total Class Hours' for each of now 100
students. Cell in ColB to the right of 'Total Class Hours' adds the hours
from ColAB for each student. ColAB contains totals for each weekday for
each class attended by each student.

As I added or removed classes (I have macros to do this), I expected that
the SUM() formulas would adjust (and they usually did), but for some
unknown reason at some unknown time, they didn't. I don't want to have to
redo the formulas manually again if the same occurs.

I need something like:
For i = 1902 to 4 Step - 19
Range("B"&i).Row Formula = "SUM(AB&i-18:AB&i-1)"
Next i
I could figure out the relative numbers if they change.

or maybe some looping Offset from 'Total Class Hours' for each student

--
David

Bob Phillips wrote

For i = 1902 to 4 Step - 19
Range("B" & i).Formula = "SUM(AB" & i - 18 & ":AB" & i-1 & ")"
Next i

Put your loop controls, start and step, in variables and you are away.

Excellent. I had to put an = in front of SUM (or result was text) and
change 18 to 17 (so student name row wasn't included), but then it worked
perfectly and surprisingly fast.

Thanks.

--
David

Misread - I thought the formulas were in contiguous cells in column B - like
what you showed.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Formula row is the row where the formulas are first entered.


Sub AddFormulas()
Dim rw as Long, i as Long
Dim StepVal as Long
rw = 10 ' formula row
StepVal = 19
For i = 4 To 1902 Step StepVal

Range("B" & rw).Formula = _
"=SUM(AB" & i & ":AB" & i + _
StepVal - 3 & ")"
rw = rw + 1
Next i

End Sub

Produced the formula you show.

--
Regards,
Tom Ogilvy

"David" wrote in message
...
I just went through a tedious exercise. I discovered some formulas whose
row references were off by two (may change). I had to manually navigate
through 100 of them over nearly 1900 rows and change them. I would like
to
automate this if at some future time I make a similar discovery.

I will try to explain my data setup sufficiently to get a solution.

In ColB I have a formula every 19 rows (now) that sums values from 17
rows
(now) above it starting in row 4 in ColAB like this:
=SUM(AB4:AB20)
=SUM(AB23:AB39)
=SUM(AB42:AB58)
and so on every 19 rows (now) through (now) row 1902

The starting row will always be 4, but the number of total values to be
added (now 17) may change as I add or subtract classes from a list in
ColA.
Each range in ColA starts with the student's name, followed by the
current
class list (now 17) followed by 'Total Class Hours' for each of now 100
students. Cell in ColB to the right of 'Total Class Hours' adds the
hours
from ColAB for each student. ColAB contains totals for each weekday for
each class attended by each student.

As I added or removed classes (I have macros to do this), I expected
that
the SUM() formulas would adjust (and they usually did), but for some
unknown reason at some unknown time, they didn't. I don't want to have
to
redo the formulas manually again if the same occurs.

I need something like:
For i = 1902 to 4 Step - 19
Range("B"&i).Row Formula = "SUM(AB&i-18:AB&i-1)"
Next i
I could figure out the relative numbers if they change.

or maybe some looping Offset from 'Total Class Hours' for each student

--
David

Tom Ogilvy wrote

Misread - I thought the formulas were in contiguous cells in column B
- like what you showed.

That's ok. I'm not always as clear as I ought to be. I went with Bob's less
elegant, but concise and effective loop. Thanks for jumping in, anyway.

--
David

Here is a correction that allows you to easily adjust.

Sub AddFormulas()
Dim i As Long, numClasses As Long
Dim numStudents As Long
Dim StepValue As Long

NumClasses = 17 ' now 17
numStudents = 100 ' now 100
StepValue = NumClasses + 2
For i = numStudents * StepValue + 2 To 4 Step -StepValue
Range("B" & i).Formula = _
"=SUM(AB" & i - 1 & ":AB" & i - _
NumClasses & ")"
Next i

End Sub

--
Regards,
Tom Ogilvy





"David" wrote in message
...
Tom Ogilvy wrote

Misread - I thought the formulas were in contiguous cells in column B
- like what you showed.

That's ok. I'm not always as clear as I ought to be. I went with Bob's
less
elegant, but concise and effective loop. Thanks for jumping in, anyway.

--
David

Tom Ogilvy wrote

Here is a correction that allows you to easily adjust.

Sub AddFormulas()
Dim i As Long, numClasses As Long
Dim numStudents As Long
Dim StepValue As Long

NumClasses = 17 ' now 17
numStudents = 100 ' now 100
StepValue = NumClasses + 2
For i = numStudents * StepValue + 2 To 4 Step -StepValue
Range("B" & i).Formula = _
"=SUM(AB" & i - 1 & ":AB" & i - _
NumClasses & ")"
Next i

End Sub

Cool. Now I'm going with yours <g

--
David

Tom Ogilvy wrote

Here is a correction that allows you to easily adjust.

Since number of students = number of printed pages:
numStudents = ExecuteExcel4Macro("Get.document(50)")

Since I have a range Name for one student to use in other routines:
numClasses = Range("Name_Copy").Rows.Count - 2

--
David

Tom Ogilvy wrote

Here is a correction that allows you to easily adjust.

Even better, I remembered I have Named Ranges "Classes" and "Students" that
result in needed numbers for .Rows.Count

--
David

You should consider redesigning your data setup to follow the rules of
relational database design. Put the student info in one table, the
class info in another table, and the student-class relationship in a
third table.

Not only will you not have to muck around changing formulas as the
number of students / classes / student-classes relationship change but
you will also have the power of various tools designed for use with
lists and relational tables at your disposal (MS Query and PivotTables
to name two).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , dturner4_1999
@yahoo.com says...
I just went through a tedious exercise. I discovered some formulas whose
row references were off by two (may change). I had to manually navigate
through 100 of them over nearly 1900 rows and change them. I would like to
automate this if at some future time I make a similar discovery.

I will try to explain my data setup sufficiently to get a solution.

In ColB I have a formula every 19 rows (now) that sums values from 17 rows
(now) above it starting in row 4 in ColAB like this:
=SUM(AB4:AB20)
=SUM(AB23:AB39)
=SUM(AB42:AB58)
and so on every 19 rows (now) through (now) row 1902

The starting row will always be 4, but the number of total values to be
added (now 17) may change as I add or subtract classes from a list in ColA.
Each range in ColA starts with the student's name, followed by the current
class list (now 17) followed by 'Total Class Hours' for each of now 100
students. Cell in ColB to the right of 'Total Class Hours' adds the hours
from ColAB for each student. ColAB contains totals for each weekday for
each class attended by each student.

As I added or removed classes (I have macros to do this), I expected that
the SUM() formulas would adjust (and they usually did), but for some
unknown reason at some unknown time, they didn't. I don't want to have to
redo the formulas manually again if the same occurs.

I need something like:
For i = 1902 to 4 Step - 19
Range("B"&i).Row Formula = "SUM(AB&i-18:AB&i-1)"
Next i
I could figure out the relative numbers if they change.

or maybe some looping Offset from 'Total Class Hours' for each student

Tushar Mehta wrote

You should consider redesigning your data setup to follow the rules of
relational database design. Put the student info in one table, the
class info in another table, and the student-class relationship in a
third table.

Thanks for your input, but I've put a lot of work into getting this book
set the way I want and what I want it to do. My query was about a minor
part of its overall function and I only sought to make it easier to correct
a rare, but possible glitch.

--
David

In article , dturner4_1999
@yahoo.com says...
Tushar Mehta wrote

You should consider redesigning your data setup to follow the rules of
relational database design. Put the student info in one table, the
class info in another table, and the student-class relationship in a
third table.

Thanks for your input, but I've put a lot of work into getting this book
set the way I want and what I want it to do. My query was about a minor
part of its overall function and I only sought to make it easier to correct
a rare, but possible glitch.


Not a problem. I shared the right way to approach this issue. You, of
course, are welcome to ignore the suggestion.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions