Dynamic Function VBA

[tuckertubaman]

Hello,
I'm new to Mr. Excel and have a question about writing a function in VBA.

I need to insert a formula into an excel cell using "ActiveCell.Formula". Here's all I need it to do: find the difference between values. Here's the catch- The location of the values is always changing as I add new data.
Background:
Assume I'm inserting a formula into "A1" and C = 35 (but this value is dynamic)

I'd like a formula something similar to "=RC[C-9]-RC[2*C-18]"

I realize this is improper but it's closest to what I'd like it to do: From the active cell, take the value C-9 (or 35-9=26 currently) columns over and subtract from 2*C-18 (or 70-18=52 currently) columns over.

Any help would be greatly appreciated!

 

[ranman256]

1. the basics of data is everything is in its own column. So why is your data column keep changing?
If so, why not have say,column A be 'the data' permenant column where the formula looks. and if your data today is actually in col C then set formula in A.formula =C
tomorrow A.formula =D
Instead of changing the formulas, change A to point to the data.

2. if you want the formula method, record a macro, type in the formula, stop macro. Now you can see what you must use in VBA.

 

[tuckertubaman]

Thanks for the help.
This file is a large file containing month-end data. It's updated monthly by inserting a few new columns for month-end data, and I'm creating a macro to make these updates easier. This formula I'm attempting to create is finding the difference between the two added columns. Like I said, it's an easy formula, but the references change every month since the columns are shifting by adding new month-end data. The location of the two referenced columns are always C-9 and 2*C-18 rows away, since C increases by 1 every month.

I don't think I can do the record macro method, because like I said above, come next month, the columns will have shifted over.

Any suggestions?

 

[tuckertubaman]

I still would appreciate some help on this issue if anyone can lend some advice. Thanks!