将整数变量传递给vba数组公式 [英] pass an integer variable into vba array formula

问题描述

我正在尝试将整数值传递给数组公式，下面是代码:-

I am trying to pass integer value to array formula, below is the code:-

cnt = 150
cnt2 = 10
Range("H" & cnt2).Select

Selection.FormulaArray = "=INDEX(Input_Sheet!R1C8:R[" & cnt & "]C8,MATCH(1,(backbone_new!RC[-4]=Input_Sheet!R1C2:R[" & cnt & "]C2)*(backbone_new!RC[-3]=Input_Sheet!R1C4:R[" & cnt & "]C4),0))"

我在电子表格中得到以下输出:

I am getting below output in spreadsheet :

=INDEX(Input_Sheet!$H$1:$H160,MATCH(1,(backbone_new!D10=Input_Sheet!$B$1:$B160)*(backbone_new!E10=Input_Sheet!$D$1:$D160),0))'

整数cnt值为150，但公式中的值为160.有什么想法吗?

Integer cnt value is 150 but I am getting 160 in the formula. Any thoughts?

推荐答案

当您使用[& cnt&"]时，将偏移150行.当您在第10行中选择一个单元格时，您最终会在第160行中结束.

As you use [" & cnt & "] you offset by 150 rows. As you selected a cell in the 10th row, you end up in the 160th row.

[]表示偏移量.如果您想要实际的行，请按照以下步骤操作:

The [ ] means offset. If you want the actual row, do it like this:

cnt = 150
cnt2 = 10
Range("H" & cnt2).Select

Selection.FormulaArray = "=INDEX(Input_Sheet!R1C8:R" & cnt & "C8,MATCH(1,(backbone_new!RC[-4]=Input_Sheet!R1C2:R" & cnt & "C2)*(backbone_new!RC[-3]=Input_Sheet!R1C4:R" & cnt & "C4),0))"

即只需删除[].

这篇关于将整数变量传递给vba数组公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！