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]

The code with "mypath" is:

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

End Sub

And this is not working as well.

 

[Domenic]

That's because the FormulaArray property has a character limit of 255. Try...

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

...which has 246 characters.

 

[asad]

It is still not working. It still gets highlighted.

 

[asad]

Here is the whole code, but not working.

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

End Sub

 

[Domenic]

I think we'll need to use two place holders for parts of the formula, and then use the Replace method of the Range object to add them back in. Note that the first part of the formula uses the R1C1 reference style, since the FormulaArray property requires it, and the A1 reference style for the Replace method.

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] AsadFormula()

    [color=darkblue]Dim[/color] myPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] myFormulaPart1 [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] myFormulaPart2 [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] myFormulaPart3 [color=darkblue]As[/color] [color=darkblue]String[/color]

    myPath = "F:\ALI\MrExcel\Footscray Roster\[Leave allocation 2011 Master.xls]Leave Approved"
    
    myFormulaPart1 = "=IF(RC[-6]<>""A/L"","""",""A/L ""&TEXT(MIN(IF('" & myPath & "'!R15C9:R215C9=RC[-2],X_X_X)),""dd/mm/yyyy""))"
    myFormulaPart2 = "IF('" & myPath & "'!$I$4:$FQ$4>=BJ9,IF('" & myPath & "'!$I$15:$FQ$215="""",Y_Y_Y))))"
    myFormulaPart3 = "'" & myPath & "'!$I$4:$FQ$4-7"
    
    [color=darkblue]With[/color] Range("G8")
        .FormulaArray = myFormulaPart1
        .Replace "X_X_X))", myFormulaPart2
        .Replace "Y_Y_Y", myFormulaPart3
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    Range("G8").Copy
    Range("G9:G10").PasteSpecial xlPasteFormulas

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[shg]

I'm not a SendKeys advocate, but this is one place I use it.

Sub InsertLongArrayFormula(r As Range, _
                           sFrm As String, _
                           Optional sFmt As String = "General")
    With r
        .NumberFormat = "@"
        .Value = sFrm
        .NumberFormat = sFmt
        Application.Goto .Cells
    End With
    DoEvents
    Application.SendKeys "{F2}^+~"
End Sub

Used, for example, as

Sub AsadFormula()
    Dim sPath As String
    Dim sFrm As String
 
    mypath = "F:\ALI\MrExcel\Footscray Roster\[Leave allocation 2011 Master.xls]Leave Approved"
    sFrm = "=IF(RC[-6] < > ""A/L"","""",""A/L ""&TEXT(MIN(IF('" & mypath & "'!R15C9:R215C9=RC[-2],IF('" & mypath & "'!R4C9:R4C173 > =R[1]C[55],IF('" & mypath & "'!R15C9:R215C173="""",'" & mypath & "'!R4C9:R4C173-7)))),""dd/mm/yyyy""))"
 
    ' this is where you look at the formula in the Immediate window and verify it's correct
    Debug.Print sFrm
    Stop
 
    InsertLongArrayFormula Range("G8"), sFrm
End Sub

 

[shg]

Forgot to mention: After you verify that the formula is correct, remove the Stop statement, and then close the VBE and run the sub. Otherwise you end up in the Object Browser.

 

[asad]

Thanks a ton Domenic. It does exactly what I wanted.
Thanks to shg also. But I haven't been able to get it to work so far. I will keep trying and let you know on Monday or Tuesday.

Asad

 

[Domenic]

You're very welcome! And thanks for the feedback!

 

[asad]

[FONT=Verdana][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=darkblue]Sub[/COLOR] AsadFormula()[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] myPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] myFormulaPart1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] myFormulaPart2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
[FONT=Verdana]   [COLOR=darkblue]Dim[/COLOR] myFormulaPart3 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR][/FONT]
 
[FONT=Verdana]   myPath = "F:\ALI\MrExcel\Footscray Roster\[Leave allocation 2011 Master.xls]Leave Approved"[/FONT]
 
[FONT=Verdana]   myFormulaPart1 = "=IF(RC[-6]<>""A/L"","""",""A/L ""&TEXT(MIN(IF('" & myPath & "'!R15C9:R215C9=RC[-2],X_X_X)),""dd/mm/yyyy""))"[/FONT]
[FONT=Verdana]   myFormulaPart2 = "IF('" & myPath & "'!$I$4:$FQ$4>=[SIZE=5][COLOR=red][B]BJ9[/B][/COLOR][/SIZE],IF('" & myPath & "'!$I$15:$FQ$215="""",Y_Y_Y))))"[/FONT]
[FONT=Verdana]   myFormulaPart3 = "'" & myPath & "'!$I$4:$FQ$4-7"[/FONT]
 
[FONT=Verdana]   [COLOR=darkblue]With[/COLOR] Range("G8")[/FONT]
[FONT=Verdana]       .FormulaArray = myFormulaPart1[/FONT]
[FONT=Verdana]       .Replace "X_X_X))", myFormulaPart2[/FONT]
[FONT=Verdana]       .Replace "Y_Y_Y", myFormulaPart3[/FONT]
[FONT=Verdana]   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR][/FONT]
 
[FONT=Verdana]   Range("G8").Copy[/FONT]
[FONT=Verdana]   Range("G9:G10").PasteSpecial xlPasteFormulas[/FONT]
 
[FONT=Verdana][COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

One more question Domenic. In the code, you have actually used BJ9. If I want to use an absolute reference like $BC$1, do I just replace BJ9 with $BC$1 or do I have to use some ref such as R1C55. But with R1C55, when I copy and paste the formula to 8 different ranges, that ref will change along with the range. Isn't it?
With you code, when I copy pasted it to another range, BJ9 changed for every cell in G column and correct result was not produced.
Sorry for this minor problem to be put through to you, but I didn't want to alter your code for fear of rendering it non-working.

Asad