VBA to change mass formulas

[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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.

 

[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.

 

[Tom Ogilvy]

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

 

[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.

 

[Tom Ogilvy]

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]

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

 

[Tushar Mehta]

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

 

[David]

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.

 

[Tushar Mehta]

Tushar Mehta wrote


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