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

 

[Domenic]

I haven't looked at the rest of your code, but the line in red should be...

Range("H8:H35").Formula = "=IF(A8<>$A$5,[COLOR="Red"]""""[/COLOR],INDEX($F$98:$F$119,MATCH(C8,$H$98:$H$119,0)))"

 

[asad]

Thanks Domenic, It is working now .

Thanks for all your help.

Asad

 

[Domenic]

You're welcome! Glad I could help!

 

[asad]

Hello Domenic,
Could you please help me again with one more issue with the same code?
I wanted the file to work with the link to the other file that you made with your code. The problem will arise when we put the source file in a folder only accessible if the user is logged on to the network as the Operations Manager. If anybody logs on to the same computer, he cannot access that folder. The file with the link updates values from that source file only and only if the source file is accessible to the user. Otherwise it gives a message that source file is not accessible and all the values remain blank.

How can I get the code to onnect to the file within the protected folder?
I do have the option of putting in user name and password within your code and make the VBA code also password protected so that no user can open the code and get the password. But how do I do it?


Asad

 

[Domenic]

Hi Asad,

I'm afraid I'm not able to help you with this one. Try starting a new thread and hopefully someone will be able to help.

Cheers!

 

[asad]

No problem Domenic. You have been very helpful right through. Thanks for all your help.

Asad