Help in writing dynamic formula in vba

  • Thread starter Thread starter vicky
  • Start date Start date
V

vicky

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
 
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:B65536)"
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.
 
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
 
Back
Top