Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Formula with Dynamic Address | Excel Worksheet Functions | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Excel Programming | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Writing dynamic code - problem with VBA project protection | Excel Programming |