D
David
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
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