Update a Macro for each line.

[Mike_in_IT]

I have a Macro that uses a simple formula. I have to update it everyday though as I'm adding a line everyday. the code is this:
ActiveCell.FormulaR1C1 = "=(R[-89]C-RC[-1])/R[-89]C"
What turns up in the spread sheet is this:
=(D2-C91)/D2
The two "D2"s stay the same as more rows are added only the C91 will change, its the row that I'm on. When I add a line tomorrow I'll be on row 92 so the spread sheet will say this:
=(D2-C92)/D2
and the Macro will have to be updated to this:
ActiveCell.FormulaR1C1 = "=(R[-90]C-RC[-1])/R[-90]C"

What I need to know is if there is a way to just hard code the "D2" in instead of the row shift reference? It would be much better to just run the macro and not have to update it first every time. I'm sure there's a way to do it, I just can't figure it out.

[Mallycat]

There is undoubtably a better way, but I doubt what you are asking for is it. Why does the formula need to change each day? Why not turn your data into a table, and have use sum to add the table totals.

[ragulduy]

You can "hard code" cells into R1C1 references by omitting the brackets. For your example:

Please Login or Register  to view this content.

[Mike_in_IT]

@Mallycat - I'm not summing the totals of the columns I'm collecting the numbers and using them for trend analysis. Each day there is a different set of numbers for the 140 computers that I'm tracking. I'm also turning the number into a percent and creating graphs out of them based on the days.

@yudlugar - thanks that did it!

[Mike_in_IT]

@yudlugar - shoot that didn't fix it. It looked like it did till I was checking my numbers. Hard coding only worked for the first one after that I am copy/pasting the formula across the columns which then they each have the wrong reference number. Here is an image of the spread sheet:
Macro Problem.PNG
It's the columns in read that I'm applying the formula to. I am adding the last number in the blue column then running a macro that applies the formula and then copy/pastes it into all the other red columns. It's supposed to take the value under the drive letter at the top and subtract the blue number then divide by the number under the drive letter. This is giving me the % of space used for each drive each day. When I run the code that you suggested it only used the value from D2 in every formula across the whole page. As shown in the screen shot I changed it back so that each column uses it's own again. So the second one will use G2 the third one will use J2, the fourth one will use M2, and so forth. I don't want to hard code all of it that's why I originally used a reference to get the correct number. But then I had to change the distance that the reference referred to every day. I am still hoping to avoid that.

[JosephP]

if you need a relative column change the code slightly to

Please Login or Register  to view this content.

[Mike_in_IT]

@JosepgP - Perfect that did, now when I copy/paste it takes the correct number for the formula. Thank you.

And Thanks to all who helped.