xlsetcell as an array...

[only1platinum]

Hey guys. Been a while since I couldn't find an answer already listed in the forums but I have searched this time and can't seem to find an answer. Feel free to advise a better way of going about this as well. Always open to suggestions.

What I need to do is sum all the unique values of an excel file. But said excel file is generated within the script from a number of operations on different text files. What I need to do is in put the following formula in to a cell as an array (In excel that means you have to hit ctrl+shift+enter to set the cell up to calculate correctly.)

=SUM(IF(FREQUENCY(IF(LEN(A2:A500)>0,MATCH(A2:A500,A2:A500,0),""), IF(LEN(A2:A500)>0,MATCH(A2:A500,A2:A500,0),""))>0,1))

After pressing the needed key combination excel adds a {} around the entire formula to designate it as an array however just adding this in to the formula does not work when using XLSetCell command.

I would prefer not to use the keyboard to interact onscreen with the excel sheet if it can be avoided.

Surely there is someone much smarter than myself out there and has needed to do this before. Thanks for all the help guys.

Below is the code I am using to input the cell too BTW.

Code: Select all

    //Display Number of Orders
    
    Let>CountOrders==SUM(IF(FREQUENCY(IF(LEN(A2:A500)>0,MATCH(A2:A500,A2:A500,0),""), IF(LEN(A2:A500)>0,MATCH(A2:A500,A2:A500,0),""))>0,1))
    XLSetCell>xlBook,%filename_without_extension%,1,74,CountOrders,Result6
    XLSave>xlBook,S:\Amazon Sales Sheets\Raw\%filename_without_extension%\%filename_without_extension%.xls
    XLGetCell>xlBook,%filename_without_extension%,1,74,CountOrders,Result6
    MessageModal>%CountOrders%

[Marcus Tettmar]

I think to do this from a Macro Scheduler script you are going to need to use VBScript. First I would record doing it in Excel. Then look at the VBA it produces and then we can work out how to translate it to VBScript to use within the macro:

http://help.mjtnet.com/article/19-conve ... o-vbscript

[hagchr]

Hi, in VBS / Excel there is a range property - FormulaArray - that can be used to define an array formula, in the case below placing it in cell "E8" in "Sheet1" in "test.xlsx".

The original formula had to be modified slightly to avoid confusion with the quotes. Hope if helps.

Code: Select all

VBSTART
Sub updExcel
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open("C:\Users\Christer\Desktop\test.xlsx")
    objExcel.Application.Visible = True
    objExcel.Worksheets("Sheet1").Activate
    tmp = "=SUM(IF(FREQUENCY(IF(LEN(A2:A500)>0,MATCH(A2:A500,A2:A500,0)," & """""" & "), IF(LEN(A2:A500)>0,MATCH(A2:A500,A2:A500,0)," & """""))>0,1))"
    objExcel.Range("E8").FormulaArray = tmp
End Sub
VBEND

VBRun>updExcel