Help in writing dynamic formula in vba
 

I want to write a formula in a dynamic way in vba . i have formula in
excel which i want to write it in vba . my formula is something like
this in column A . i want to make the columns dynamic i.e in column A
i have this formula

1) =SUMIF('Test'!AI4:AI65536,"CORE",'test'!B4:B65536)

so in column b i want the formula to be

2) =SUMIF('Test'!AJ4:AJ65536,"CORE",'test'!C4:C65536)

and so on . IN excel we can do this by just dragging . but i want to
implement this in vba .

i.e when i am looping a "for each " loop in col A Formula 1 has to be
applied

IN Column B Formula 2 has to be applied

Help in writing dynamic formula in vba
 

Vicky,

There's no need to loop try this. It fills as far as column I so change to
suit

Range("A1").Formula = "=SUMIF('Test'!$AI$4:AI65536,""CORE"",'test'!B4:B6 5536)"
Range("A1").AutoFill Destination:=Range("A1:I1"), Type:=xlFillDefault
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"vicky" wrote:

I want to write a formula in a dynamic way in vba . i have formula in
excel which i want to write it in vba . my formula is something like
this in column A . i want to make the columns dynamic i.e in column A
i have this formula

1) =SUMIF('Test'!AI4:AI65536,"CORE",'test'!B4:B65536)

so in column b i want the formula to be

2) =SUMIF('Test'!AJ4:AJ65536,"CORE",'test'!C4:C65536)

and so on . IN excel we can do this by just dragging . but i want to
implement this in vba .

i.e when i am looping a "for each " loop in col A Formula 1 has to be
applied

IN Column B Formula 2 has to be applied





.

Help in writing dynamic formula in vba
 

Try this one.

Sub fillinformula()
Dim rng As Range

For Each rng In Columns("A").SpecialCells(xlCellTypeFormulas)
rng.AutoFill Destination:=rng.Resize(1, 2)
Next

End Sub

Keiji

vicky wrote:
I want to write a formula in a dynamic way in vba . i have formula in
excel which i want to write it in vba . my formula is something like
this in column A . i want to make the columns dynamic i.e in column A
i have this formula

1) =SUMIF('Test'!AI4:AI65536,"CORE",'test'!B4:B65536)

so in column b i want the formula to be

2) =SUMIF('Test'!AJ4:AJ65536,"CORE",'test'!C4:C65536)

and so on . IN excel we can do this by just dragging . but i want to
implement this in vba .

i.e when i am looping a "for each " loop in col A Formula 1 has to be
applied

IN Column B Formula 2 has to be applied