Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to change mass formulas
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mass Forumla Change | Excel Worksheet Functions | |||
How do I mass update formulas? | Excel Discussion (Misc queries) | |||
Mass Change Hyperlinks | Excel Programming | |||
Mass Change Across Many WorkBOOKS | Excel Discussion (Misc queries) | |||
mass changes in formulas | Excel Programming |