工作表函数中的数组

Question

当我运行下面的代码时，我只得到第一个匹配的结果，在这种情况下，只得到"；法国"；。有人知道如何将转置合并到数组中吗？我尝试了很多不同的方法。

Sub YTDRoutes()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim EuropeArray As Variant
EuropeArray = Array("France", "Egypt", "Belgium", "Greece", "Italy", "Lithuania", "Netherlands", "Norway", "Poland", "Portugal", "Spain", "Turkey", "United Kingdom")

Worksheets("T Slide").Cells(9, 5) = Application.CountIfs(Worksheets("RIMPORT").Range("Ak1:Ak25000"), Worksheets("T Slide").Cells(4, 2).Value, Worksheets("RIMPORT").Range("Am1:Am25000"), EuropeArray, Worksheets("RIMPORT”).Range("ap1:Ap25000"), ">=" & CLng(Worksheets("T Slide").Cells(1, 5).Value), Worksheets("RIMPORT").Range("ap1:Ap25000"), "<=" & CLng(Worksheets("T Slide").Cells(2, 5).Value))
End Sub

Answer 1

EuropeArray是一个索引从0到n-1的一维数组，您需要指示所需的值。EuropeArray(0(是"0"；法国"；，EuropeArray(1(是"；埃及"；。您可以在同一行列出每个索引值，用逗号分隔。注意，n-1表示EuropeArray数组中最后一个值的数值索引：

Range("Am1:Am25000"(、EuropeArray。。。EuropeArray(n-1(，工作表("RIMPORT"(。范围…

Answer 2

将其包装在Application.Sumproduct(...)中

Sub YTDRoutes()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim EuropeArray As Variant
EuropeArray = Array("France", "Egypt", "Belgium", "Greece", "Italy", "Lithuania", "Netherlands", "Norway", "Poland", "Portugal", "Spain", "Turkey", "United Kingdom")

Worksheets("T Slide").Cells(9, 5) = Application.SumProduct(Application.CountIfs(Worksheets("RIMPORT").Range("Ak1:Ak25000"), Worksheets("T Slide").Cells(4, 2).Value, Worksheets("RIMPORT").Range("Am1:Am25000"), EuropeArray, Worksheets("RIMPORT”).Range("ap1:Ap25000"), ">=" & CLng(Worksheets("T Slide").Cells(1, 5).Value), Worksheets("RIMPORT").Range("ap1:Ap25000"), "<=" & CLng(Worksheets("T Slide").Cells(2, 5).Value)))
End Sub