How can I put this array formula using VBA?

[asad]

Hello Guys,

I got the following array formula given to me on this forum:

{=IF(A7 < > A$5,"",$A$5&" "&TEXT(MIN(IF('O:\Operations Supervisor\`Rosters 2011\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$15:$I$215=$E7,IF('O:\Operations Supervisor\`Rosters 2011\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$4:$FQ$4 > =$BC$1,IF('O:\Operations Supervisor\`Rosters 2011\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$15:$FQ$215="",'O:\Operations Supervisor\`Rosters 2011\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$4:$FQ$4-7)))),"dd/mm/yyyy"))}

It is working fine.

I want to put this formula in the ranges G8:G37, G39:G63, G67:G72 and so on. There are 8 ranges like this all in column G. Is there a way to use VBA to do this for me? The reason for this is that I got another macro that clears column G and then I am left with no formulas in there. I will add this extra bit of VBA to the existing macro so that I don't have to fill this formula in manually every time I run the original macro.

Thanks for your help.
Asad

 

[asad]

Bump

 

[asad]

 

[asad]

I tried following code, but is not working.

[COLOR=black][B]Sub AsadFormula()[/B][/COLOR]
[COLOR=red]Range("G8:G10").FormulaR1C1 = "IF(RC[-6]<>"A/L","","A/L "&TEXT(MIN(IF('F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$15:$I$215=RC[-2],IF('F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$4:$FQ$4 > =R[1]C[55],IF('F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$15:$FQ$215="",'F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$4:$FQ$4-7)))),"dd/mm/yyyy"))"
[/COLOR]End Sub

The part on top "Sub AsadFormula()" gets highligghted in yellow and the formula goes to red colour.
What is wrong with this formula?
Any help?

Asad

 

[asad]

Now I changed it to

Sub AsadFormula()
Range("G8:G10").FormulaArray = "=IF(RC[-6]<>""A/L"","",""A/L ""&TEXT(MIN(IF('F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$15:$I$215=RC[-2],IF('F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$4:$FQ$4>=R[1]C[55],IF('F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$15:$FQ$215="",'F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$4:$FQ$4-7)))),""dd/mm/yyyy""))"
End Sub

Still is not working. Now, just the formula part gets highlighted in yellow. Any ideas please.

Asad

 

[Domenic]

Try...

[font=Verdana]Range("G8").FormulaArray = "=IF(RC[-6]<>""A/L"","""",""A/L ""&TEXT(MIN(IF(R15C9:R215C9=RC[-2],IF(R4C9:R4C173>=R[1]C[55],IF(R15C9:R215C173="""",R4C9:R4C173-7)))),""dd/mm/yyyy""))"

Range("G8").Copy

Range("G9:G10").PasteSpecial xlPasteFormulas
    [/font]

For multiple ranges, replace...

[font=Verdana]Range("G9:G10").PasteSpecial xlPasteFormulas[/font]

with

[font=Verdana]Range("G9:G37,G39:G63,G67:G72").PasteSpecial xlPasteFormulas
    [/font]

 

[asad]

Try...

[FONT=Verdana]Range("G8").FormulaArray = "=IF(RC[-6]<>""A/L"","""",""A/L ""&TEXT(MIN(IF(R15C9:R215C9=RC[-2],IF(R4C9:R4C173>=R[1]C[55],IF(R15C9:R215C173="""",R4C9:R4C173-7)))),""dd/mm/yyyy""))"[/FONT]
 
[/QUOTE]
 
But how will it look at file [QUOTE]'F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!$I$15:$I$215[/QUOTE]
 
The code that you gave will not pick the file from the location I showed above, will it?
 
Asad

 

[Domenic]

Add the appropriate path/workbook name/sheet name...

 

[asad]

I tried and it gave me an error. The code is

Sub AsadFormula()
Range("G8").FormulaArray = "=IF(RC[-6] < > ""A/L"","""",""A/L ""&TEXT(MIN(IF('F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!R15C9:R215C9=RC[-2],IF('F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!R4C9:R4C173 > =R[1]C[55],IF('F:\ALI\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved'!R15C9:R215C173="""",R4C9:R4C173-7)))),""dd/mm/yyyy""))"
Range("G8").Copy
Range("G9:G10").PasteSpecial xlPasteFormulas
    
End Sub

The line with the formula gets highlighted in yellow.
What is it taht I am doing wrong? Can't figure it myself. Your help is much appreciated.

Asad

 

[asad]

I tried giving name to path as "mypath" and inserted it in the formula, but same result.