PDA

View Full Version : Dynamic Formula put in formula with VBA



aguice
07-16-2010, 08:54 AM
I have VBA code creating X number of weeks in columns. Each week consists of M-F(Columns 1-5), and then a total of that weeks numbers(Column 6). And then say X = 2, I would have a 'Grand Total' in Column 13. Column 6 and 12 have totals from M-F(got that formula from Selection.FormulaR1C1 = "=SUM(R[0]C[-1]:R[0]C[-5]"), but then I need a formula to put in Column 13. The formula for X = 2 would be this, "=SUM(R[0]C[-1],R[0]C[-7])". How would I write the formula so that it will change with X. (ex. X=3, "=SUM(R[0]C[-1],R[0]C[-7],R[0]C[-13])" )

Any and all help is appreciated!

Bob Phillips
07-16-2010, 09:16 AM
Where does X come from? How does M-F make 5 columns?

An example would make it all clearer.

aguice
07-16-2010, 09:32 AM
X comes from a user inputbox that pops up and the user puts an integer in the text field.

The actual process is making all 7 days of the week for each week, then converting saturday(using a weekday(currentcell) = 7) to be the column for M-F totals. and then deleting the sunday column.

After the loop is done with all X, I have just a line of code labeling the last necessary column as "Grand Total". And then need a way of creating the formula to write in that column to get the 'sum of the sums'(sum of the weekly totals).

I am new to this forum, can we post files up here? or what kind of example you looking for?

Bob Phillips
07-16-2010, 10:07 AM
You can post files. Use the Go Advanced button, then manage files. Talk through an example with that workbook data.