Add formula to macro

Hello,

I have a macro which creates all months for a year. I need a formula on every sheet after January in AJ5, AJ9, AJ13 and so on (step is 4), till AJ317.

Formula is simple, It only needs to display result of cell, of previous month. Example for AJ5, formula is :

Please Login or Register  to view this content.

That was for January 2013, in same cell on next sheet (March 2013) should be:

Please Login or Register  to view this content.

How can I do that in VBA ?

Maybe:

Please Login or Register  to view this content.

Thanks for reply, interesting code, but I get #Name error, and window for updating values. What is wrong, I would send sample worksheet but file is too large, over 1.1 Mb

Here It is, I had to erase a lot of things

I've allready made button for macro on second sheet (Februar). Also, first cell to be filled is AJ5, not AJ9 !

Maybe:

Please Login or Register  to view this content.

One thing your sum range was merged so I changed the formula a little. Hope it doesn't matter.

I tried that also. This time formula is inserted in AJ5 of February, then code jumps through all sheets. All other cells in every sheet are without formula. You think merged cells could be the cause of malfunction ?

I tested it on your sample provided in Post#4 and it worked for me?

I'm sorry, but It's not working for me. I want formulas to be on every sheet in desired range, but formula provided in sample from post #4 requires some updating after executing code, and It inserts formula on February sheet in AJ5 cell only, with #Name error.


Your code from post #5 doesn't open any window for updating, and inserts correctly formula into February sheet in AJ5 cell, but only that cell.


Maybe I don't have properly set Excel and something is blocking my code ? My setting for macros security are middle, and on the Trusted Publishers tab have selected the "Trust all installed add-ins and templates". Also tried with high setting, and everything same. Could be that I am missing some add-in ?

I don't know what too say. I ran the code on your sample and didn't have a problem. However the formula I used was modified.

like this - =SUM('September 2014'!A5:A6)

It had to be because you have merged cells.

Nope, still not working. Although I see that I made mistake and didnt tell everything :

Range for AJ5-318 is step 4, but also cells from A5-318 are step 4.... So it should be like this :

AJ5 in February...result of A5 from previous sheet - January - (or A5:A6 with merged cell, but I can remove that);
AJ9....result of A9 from previous sheet...
AJ13...result of A13, and so on !

So, range with step must be also in the formula Itself

That's what it does for me when I tested it. I can't tell why it's not working for you?

Ok, won't bother you anymore. I'll try differently, maybe there is something wrong with X variable. But I don't see how your code would work If you only have range of A5:A6 in formula.

thanks for help !

Sorry. Maybe someone else can help. Hope you find a solution.

John, I have researched your code again. Guess like you missunderstood, your code loops through each sheet after first one, but It inserts formula on first sheet in AJ5, on second sheet AJ9, third sheet AJ13 and so on...And this is not what I want.

So, I have manage to create code using SheetOffset function, but now I have problems with looping. Loop must be through sheets 2 to sheets.count, and the formula must be inserted on each sheet through column AJ with step 4, beginning in AJ5, coresponding to previous sheet in column A with same range.

This is the code that I'm into, for better understanding:

Please Login or Register  to view this content.

Code works, but I would need to write It for whole column, which is inflicting 80 rows. ANYBODY have an idea how to perform correct looping ?

Found other way to solve my problem...Inserted formula in first sheet, which I copy and then create new ones with macro, and then delete formula for first sheet, works fine. Used Sheetoffset function as said. Thanks for help although !