dynamic formula with VBA

Hi all,

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

    With ThisWorkbook.Sheets("GFE+").Range("E1:R250")
        .Formula = "=IF($B1="""","""",INDEX(GFE_Archived_Data,$B1,COLUMN(D:D)))"
    End With

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

=IF($B1="","",INDEX(GFE_Archived_Data,$B1,COLUMN(D:D)))

e.g. formula in F10:

=IF($B10="","",INDEX(GFE_Archived_Data,$B10,COLUMN(E:E)))

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

COLUMN(C[-1])

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

e.g.

The formula in E1 is technically just:

=IF($B1="","",INDEX(GFE_Archived_Data,$B1,4))

Then in F1 it would be:

=IF($B1="","",INDEX(GFE_Archived_Data,$B1,5))

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!!