Hi there,
I have put together a spreadsheet that works perfectly. However I want to "Macro-ise" it to take in new log files and analyse them.
As part of this I wrote the following array formula:
{=AVERAGE((IF(OFFSET('T02'!$U$2,1,0,COUNT('T02'!$A$2:$A$64000),1)=B$2),(OFFSET('T02'!$V$2,1,0,COUNT('T02'!$A$2:$A$64000),1)),"")),"")))}
Which works just fine. However, when I try to use VBA to enter this formula in for me the macro doesn't run. It gives me "Unable to set the FormulaArray property of the range class". I have spent a full day at work on google trying to find a solution but am at my wits end!
Sorry, what I have in the VBA code is:
Range("C26").FormulaArray = "=AVERAGE((IF(OFFSET('T02'!$U$2,1,0,COUNT('T02'!$A$2:$A$64000),1)=B$2),(OFFSET('T02'!$V$2,1,0,COUNT('T02'!$A$2:$A$64000),1)),"")),"")))"
Any help/ideas??
Thanks!
I have put together a spreadsheet that works perfectly. However I want to "Macro-ise" it to take in new log files and analyse them.
As part of this I wrote the following array formula:
{=AVERAGE((IF(OFFSET('T02'!$U$2,1,0,COUNT('T02'!$A$2:$A$64000),1)=B$2),(OFFSET('T02'!$V$2,1,0,COUNT('T02'!$A$2:$A$64000),1)),"")),"")))}
Which works just fine. However, when I try to use VBA to enter this formula in for me the macro doesn't run. It gives me "Unable to set the FormulaArray property of the range class". I have spent a full day at work on google trying to find a solution but am at my wits end!
Sorry, what I have in the VBA code is:
Range("C26").FormulaArray = "=AVERAGE((IF(OFFSET('T02'!$U$2,1,0,COUNT('T02'!$A$2:$A$64000),1)=B$2),(OFFSET('T02'!$V$2,1,0,COUNT('T02'!$A$2:$A$64000),1)),"")),"")))"
Any help/ideas??
Thanks!
Last edited: