copy a formula using VBA

[Colleen45]

I would like to copy the formula below using VBA
The formula is in K5, and I would like it copied down to K111
The only change for each line is that 1.xlsm, must change to 2.xlsm then to 3.xlsm etc until 107.xlsm

=SUMPRODUCT(COUNTIF(BR8,'C:\Collection Dev\Stock Register\2010-2011\[1.xlsm]Annual Expenditure'!$AI$15:$AL$1000))

 

[VoG]

Try like this

Sub test()
Range("K5").AutoFill Destination:=Range("K5:K111")
End Sub

 

[nightcrawler23]

=SUMPRODUCT(COUNTIF(BR8,'C:\Collection Dev\Stock Register\2010-2011\INDIRECT("[" & L5 & ".xlsm]Annual Expenditure'!$AI$15:$AL$1000")))

I USED THE INDIRECT COMMAND TO MAKE THE STRING AN ADDRESS. L5 TO L111 WILL HAVE 1 2 3 .....

 

[Colleen45]

I think maybe the point is being missed, the formula must remain as it is, with only the workbook number increasing, ie 1.xlsm, 2.xlsm etc

 

[VoG]

Try

Sub test()
Dim i As Long
For i = 5 To 111
    Range("K" & i).Formula = "=SUMPRODUCT(COUNTIF(BR8,'C:\Collection Dev\Stock Register\2010-2011\[" & i - 4 & ".xlsm]Annual Expenditure'!$AI$15:$AL$1000))"
Next i
End Sub

 

[nightcrawler23]

=SUMPRODUCT(COUNTIF(BR8,'C:\Collection Dev\Stock Register\2010-2011\INDIRECT("[" & L5 & ".xlsm]Annual Expenditure'!$AI$15:$AL$1000")))

I USED THE INDIRECT COMMAND TO MAKE THE STRING AN ADDRESS. L5 TO L111 WILL HAVE 1 2 3 .....

oops...

DOES THIS WORK FOR YOU

=SUMPRODUCT(COUNTIF(BR8,INDIRECT("'C:\Collection Dev\Stock Register\2010-2011\[" & L5 & ".xlsm]Annual Expenditure'!$AI$15:$AL$1000")))

 

[Colleen45]

Thanks Peter, this is exactly what I'm looking for... perfect thanks

 

[Colleen45]

I would like to do exactly the same with this formula but i'm not having much success

='C:\Collection Dev\Stock Register\2010-2011\[1.xlsm]Annual Expenditure'!$H$6

all i have at the moment is this but I'm receiving a runtime error

Range("b" & i).Formula = "=c:\collection dev\stock register\2010-2011\[" & i - 4 & ".xlsm]Annual Expenditure'!$h$6"

 

[Colleen45]

I managed to sort it out

Range("B" & i).Formula = "='[" & i - 4 & ".xlsm]Annual Expenditure'!$h$6"