dynamic formula with VBA

Hi all,

How can I write below formula to make it dynamic when inserting across the array?

Please Login or Register  to view this content.

e.g. formula in cell E1 should look like this:

Please Login or Register  to view this content.

e.g. formula in F10:

Please Login or Register  to view this content.

etc...

Yup, that also works! Learned something new again. Thanks!

Great! what I was initially looking for. Is there a way that it always starts from let's say column A and then moves one to the right? The range keeps shifting and I don't always want to change the

Please Login or Register  to view this content.

part and replace the -1 by let's say -65...

e.g.

The formula in E1 is technically just:

Please Login or Register  to view this content.

Then in F1 it would be:

Please Login or Register  to view this content.

it is basically always starting from 4 and then +1 for the next column...

Because the range the formula is applied to keeps changing now and then, let's say from Range("E1:R250") to Range("AA1:AN250") but the column number that should be returned in my INDEX formula always starts at 4!

Your original code looks fine to me. What was the problem with it exactly?

... that's embarrassing! I literally didn't try the code before, as I was 100% sure that it would just insert an identical formula in every single cell of the array without moving columns or rows.

Thanks both for helping out!!

It also fixes your later problem.

Exactly!!