Dynamic Formula Creation

Question

In Sheet1 i have one stagnant Column suppose Column "A" which gets copied again and again in next empty column in same Sheet

Like 1st it gets copied in Column "B" then find next empty column & copy data in Column "C"

--> Here my question is

I want to create formula in Sheet 2 in Column "A" where it make Formula with next filled column in Sheet1

Like

1st it makes calculation: Sheet1!A1/Sheet1!B1

After few min
it make formula in same Column "A" oF Sheet2: Sheet1!A1/Sheet1!C1

After few min

so on

Sub GoToNextCol()
    Application.ScreenUpdating = False

    Dim NextCol As Long
    NextCol = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column + 1

    Sheet1.Range("A1").CurrentRegion.Copy Sheet1.Cells(1, NextCol)

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

SJR · Accepted Answer · 2019-07-08 13:30:52Z

1

Do you mean this?

In Sheet2 cell A1

sheet2.range("a1").formula="=Sheet1!A1/Sheet1!" & cells(1,nextcol).address(0,0)

answered Jul 8, 2019 at 13:30

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NSH Over a year ago

thank you so much ..you are on my favorites list ----

NSH Over a year ago

what if i have to create this Formula @SJR --> =IF(Sheet1!B1>Sheet1!C1, IF(Sheet1!B1>Sheet1!D1, 2, 1),IF(Sheet1!B1<Sheet1!C1, IF(Sheet1!B1<Sheet1!D1, -2, -1),0)) & Further Column gets incremental like C & D changes to E & F ---- Then E & F Changes to G & H like this ....

SJR Over a year ago

It's the same principle. cells(1,nextcol).address(0,0) resolves to C1 when nextcol=3 and so on.

Collectives™ on Stack Overflow

Dynamic Formula Creation

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related