Paste info to cell in position relative to a named cell-Simpler than it sounds

I have named a cell as: "SteadyCell"

I use this code to copy informations from cells A1 and B1:

Please Login or Register  to view this content.

I would like to know how I can paste A1 at two columns before "SteadyCell" and B1 one column before "SteadyCell"

The idea is something like that:

Please Login or Register  to view this content.

--->I would also like to know how to implement this idea:
write some code on vba for adding this text in every new column : "Yield 20XX" where XX will ascending with every new column.

You can use this as code:

Please Login or Register  to view this content.

Originally Posted by RudiS

You can use this as code:

Please Login or Register  to view this content.

Very nice guys, however the code returns error: Method 'Range' of object '_Global' failed.

is it possible to implement the same thing but instead of copying doing what I wrote in my first post (--->)?

Adding the text: "Yield 20XX" where XX will be ascending with every new column!
Thanks you are amazing guys!

Where must the Yield 20XX go? Before or after SteadyCell?
Also how many entries of Yield 20XX?

Before the SteadyCell:
For the first new column lets say I want: Yield 20XX
and for the second I want: IDX 20XX

If you mean for how many years forward (hence created columns) let's say 20.

Now I use this code to always create the two new columns before "SteadyCell"

Please Login or Register  to view this content.

OK... Last question.
Do you want the macro it insert columns before the Steadycell in a loop until it reaches 20. So you will have 40 columns each with Yield 20XX and IDX 20XX.

TX ...I just need clarity

Of course ask whatever you need mate.

If you check the code:

Please Login or Register  to view this content.

Every time I run the macro it creates those two new columns. Those columns are for every year.

As for the 20 years limit. After 20 years yes there will be 40 new columns each of them with Yield 2014, IDX 2014 then Yield 2015, IDX 2015.
To rephrase it I want the first time I will use the macro to create two columns with names Yield 2014 and IDX 2014 before the steadycell,
the next time that I will use the macro I want it to create Yield 2015 and IDX 2015 which of course will be before the Steady Cell and after 2014 infos.

If it is possible I would prefer without the limit of 20 years because it is for a client!

So with the code above I can create the two columns,
now I want to automate the process of filling the first row as a title with Yield 20XX and IDX 20XX respectively.


Also please inform me if you can about the error that the code you suggested returns. Because I need to know how to refer to those cells relatively to the steadycell and not use the code above:

Please Login or Register  to view this content.

where C and D are the new columns for this run.

Thanks for your help.

Here is the code...

Please Login or Register  to view this content.

Mate, congratulations!

Only little problem, because I am from Greece, I need to write Yield in Greek language which is "Παραγωγή" but it is not readable from macros.
I noticed that by copying "Yield(in greek)" from another cell to the new one works.

So is it possible for you to alter the code so as to copy the name "Yield(in greek)" from another cell (put a cell of your choice to copy the info from) and past it in your code in the place of "Yield"
and the code will be perfect.

Sorry for delay...
I got busy with other tasks...

Try this code:

Please Login or Register  to view this content.

Thanks for the confidence you place in me

I'm not sure if you want the labels autofilled?, but try this:

Please Login or Register  to view this content.

Mate for real, you are very helpful. You understand exactly what I am asking for.

As I implement what you are giving in the problem, more questions arise.

Those two I believe are the last ones. Any help will be much appreciated.

1) The SteadyColumn that we are talking about calculates the average of the first of every new cell that we create.

How we can add on this formula the informations from every new cell, so as to calculate the average of all the years?
(If you can tell me how to do it for the first cell I will be able using the formula above to do it for every other cell)

What I want is =IFERROR(AVERAGE(A),0) then IFERROR(AVERAGE(A,C),0) then IFERROR(AVERAGE(A,C,E) .

2) This questions is related to the first one. I have an AVERAGEIFS(A,B,C) where A is the array from which the average will occur and B, C the arguments as you already know.

I have a random box with the formula above that I want every time that I run the macro to move the A to the new second column.
So the formula is AVERAGEIFS(B,x,y) then it is AVERAGEIFS(D,x,y) then it is AVERAGEIFS(F,x,y)

Is it possible for you to do those two?

Hi,

Try this.
I added the code to update the formula in the SteadyColumn cell.
As for the AVERAGEIFS() function...I'm not sure what is the source cell and what conditions are needed, so I just wrote some sample code that you can update.

Please Login or Register  to view this content.

Attached...

Very nice could you please adjust the code to pick only the first row and NOT all the columns?

Also because as SteadyColumn I set the title of average can you please adjust the code to calculate the average from above the title?

SteadyColumn.Offset(1,0) is where AVERAGE goes and from the left at SteadyColumn.Offset(1,-2 and -4) etc are the cell that average is calculated from.


Thank you very much RudiS!

Can you elaborate. Which formula must be updated and what range must it use.
TX

Of course check the attached file.
I made a little change. Check again RudiS
Attachment 316094

I only need the calculations to be adjusted. I have set the new columns etc..

Since the second calculation that I asked you move relatively is it easier to do that from excel and not with macro with a command like offset?
I try to do that and the functions averageifs and offset return error.

Please Login or Register  to view this content.

where BK is a cell after SteadyColumn that moves relatively when adding columns.


!I can send you the whole project to get the idea if you want to.

Hi,

I think it would be better to send the actual file. Then I can see exactly what you are trying to do.
Just desensitize the data and there is no need for many records, just a few will do.

Please instruct what you need done in this file based on the references, so its clear.

TX

Hi,

Try this code.
I only worked on the Yield and IDX and added code in the macro to calculate these two columns. I cannot see that the others will be affected by the additional columns? If they do...maybe with the code I added it will give an idea of how to set up.

Give the code a try and see if it runs OK.

Please Login or Register  to view this content.

Please attach the file that you test that. It's easier for me to check the file.

Attached...

Hi,

I must thank you for all the attempts to explain this formula. I do understand that you need it to shift dynamically to calculate the 5th column to the left each time the macro runs, but at the moment I am just seeing a bunch of numbers and symbols. I warned you that coding comes easier than large formulas
Unfortunately I am out of my depth here as I cannot solve something I don't understand.

I must admit that I find it strange that the offset options you built into the formula do not do the job???!! If the idea is to always reference the 5th column to the left of the formula, this should do it! In the sample file I attached, you will see it works perfectly each time you click the macro button.
Something else: I see my OFFSET(...) in the sample file is structured differently to yours. I refer to 1 cell as the anchor reference where you have a range as an anchor. I am suspicious that this might be wrong.
Instead of: =OFFSET($BK$2:$BK$3000,0,-4)
Try this: =OFFSET($BK$2,1,-4,3000,1)

Also, if it is supposed to reference the 5th column to the left, why are you using -4 in the offset?

I hope that something in what I say has a small chance in resolving this issue.