Array Formula in VBA

  • Thread starter Thread starter Bigfoot17
  • Start date Start date
B

Bigfoot17

I have been using a array formula like this to 'count' the number of cells
that =1 in one column and >25 in the second column. The file the cell is
checking is in another file.
{=SUM(('[file2.xls]Sheet3'!$H$2:$H$1800=1)*('[fiel2.xls]Sheet3'!$N$2:$N$1800>50))}

Currently when the file opens it asks if I want to update and then it checks
file2 and enters the data. Simple enough. But now I am writing code to work
at the press of a macro button, and I am not making progress.

Workbooks("file1.xls").Sheets("Sheet1").Activate
Range("B6").FormulaArray =
{=SUM(('[file2.xls]Sheet3'!$H$2:$H$1800=1)*('[file2.xls]Sheet3!$N$2:$N$1800>50))}

Any guidance is appreciated.
 
Perhaps setting Application.DisplayAlerts to False before running your code
and back to True afterwards will handle your problem. If it works for this
situation, setting DisplayAlerts to False will mean any dialog boxes that
would be have been displayed will not display and the default button will be
selected automatically (well, for your case... this works the opposite for
SaveAs).

Rick
 
Just think thru your steps logically:

1. Open the 2nd file - you can't activate the 2nd file unless it's already
open.
2. Initialize the array
3. Loop thru each cell in 2nd file, updating the array if your condition is
met.
4. Close the 2nd file.
5. Write the results to the cell(s).

Feel free to contact me direct if you have any additional questions.

(e-mail address removed)
 
I am afraid I was not clear so I will take another stab at it.

I currently have a array formula in file1.xls on sheet1 in cell B6
={=SUM(('[file2.xls]Sheet3'!$H$2:$H$1800=1)*('[file2.xls]Sheet3'!$N$2:$N$1800>50))}

This works fine, but now I need to get the same data into the same cell
(from file2) with VBA and cannot get the proper syntax.
 
Try this one. Assuming file1.xls and file2.xls are in the same folder.

Range("B6").FormulaArray =
"=SUM(([file2.xls]Sheet3!$H$2:$H$1800=1)*([file2.xls]Sheet3!$N$2:$N$1800>50))"

keiji
 
Give this person a donut! Thanks it got me where i needed to be. My final
line was:
Range("B6").FormulaArray =
"=SUM(('[file2.xls]Sheet3'!$H$2:$H$1800=1)*('[file2.xls]sheet3'!$K$2:$K$1800>25))"
I wasn't far off in figuring it out in my first post, but I was missing a
single quote and threw everything off. Thanks.

kounoike said:
Try this one. Assuming file1.xls and file2.xls are in the same folder.

Range("B6").FormulaArray =
"=SUM(([file2.xls]Sheet3!$H$2:$H$1800=1)*([file2.xls]Sheet3!$N$2:$N$1800>50))"

keiji

Bigfoot17 said:
I am afraid I was not clear so I will take another stab at it.

I currently have a array formula in file1.xls on sheet1 in cell B6:
={=SUM(('[file2.xls]Sheet3'!$H$2:$H$1800=1)*('[file2.xls]Sheet3'!$N$2:$N$1800>50))}

This works fine, but now I need to get the same data into the same cell
(from file2) with VBA and cannot get the proper syntax.
Click to expand...
Click to expand...
 
you're welcome. in this case i think it would work without single quote, but
adding single quote is better way.

keiji

Bigfoot17 said:
Give this person a donut! Thanks it got me where i needed to be. My final
line was:
Range("B6").FormulaArray =
"=SUM(('[file2.xls]Sheet3'!$H$2:$H$1800=1)*('[file2.xls]sheet3'!$K$2:$K$1800>25))"
I wasn't far off in figuring it out in my first post, but I was missing a
single quote and threw everything off. Thanks.

kounoike said:
Try this one. Assuming file1.xls and file2.xls are in the same folder.

Range("B6").FormulaArray =
"=SUM(([file2.xls]Sheet3!$H$2:$H$1800=1)*([file2.xls]Sheet3!$N$2:$N$1800>50))"

keiji

Bigfoot17 said:
I am afraid I was not clear so I will take another stab at it.

I currently have a array formula in file1.xls on sheet1 in cell B6:
={=SUM(('[file2.xls]Sheet3'!$H$2:$H$1800=1)*('[file2.xls]Sheet3'!$N$2:$N$1800>50))}

This works fine, but now I need to get the same data into the same cell
(from file2) with VBA and cannot get the proper syntax.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top