通过VBA插入数组公式 [英] Insert an Array Formula via VBA

问题描述

我正在使用VBA，我需要插入一个数组公式(如果要手动编写该数组，则需要按 Ctrl + Shift + Enter ，而不仅仅是 Enter ).当我像常规公式一样插入它时，无论如何，当我在它周围加上{}时，它也不起作用... 使用VBA编写公式的正确方法是什么?

I'm using VBA, and I need to insert an array formula (the one that if I'm writing it manually, I'll press Ctrl+Shift+Enter and not just Enter). When I'm inserting it like a regular formula it doesn't work, neither when I put it with {} around it... What's the correct way of writing that formula using VBA?

公式是这样

 =INDEX(subset!R1C1:R2472C10,MATCH(1,(RC1=subset!C1)*(RC2=subset!C2)*(RC5=subset!C5)*(RC6=subset!C6),0),10)  

推荐答案

您正在寻找可以为像这样的单元格设置的FormulaArray属性:

You're looking for the FormulaArray property that you can set for a cell like so:

Range("A1").FormulaArray = "=INDEX(subset!R1C1:R2472C10,MATCH(1,(RC1=subset!C1)(RC2=subset!C2)(RC5=subset!C5)*(RC6=subset!C6),0),10)"

在此处查看文档: http://msdn.microsoft.com/zh-cn/library/office/ff837104%28v=office.15%29.aspx

这篇关于通过VBA插入数组公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！