Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula in VBA
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$180050))} 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$180050))} Any guidance is appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula in VBA
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 "Bigfoot17" wrote in message ... 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$180050))} 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$180050))} Any guidance is appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula in VBA
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$180050))} 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula in VBA
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$180050))" keiji "Bigfoot17" wrote in message ... 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$180050))} 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula in VBA
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$180025))" 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" wrote: 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$180050))" keiji "Bigfoot17" wrote in message ... 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$180050))} 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula in VBA
you're welcome. in this case i think it would work without single quote, but
adding single quote is better way. keiji "Bigfoot17" wrote in message ... 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$180025))" 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" wrote: 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$180050))" keiji "Bigfoot17" wrote in message ... 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$180050))} 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
copy one array formula to an array range | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming |