arrays - 如何在 VBA 的公式中使用数组变量而不是范围

Question

所以，我想使用 1D 和 2D 数组来代替公式中的范围，除非我尝试过的任何方法都不起作用。如果有人可以，他们可以帮忙吗？

此外，在转置我的数据时，我不明白如何通过引用其他工作表来做到这一点。

Sub Testrun()

Cells(5, 6).Value = "=Sum([myArr])"

Set mcco = Workbooks("Book1.xlsb").Worksheets("Sheet1")
Set mcfc = Workbooks("Book1.xlsb").Worksheets("Sheet2")
Set mcfb = Workbooks("Book1.xlsb").Worksheets("Sheet3")

TR = Application.CountA(Range("A:A"))

FTNRowStart = 1
MainRowStart = 2
CVTRRowEnd = mcco.Range("A2", mcco.Range("A2").End(xlDown)).Rows.Count + 1
FCRowEnd = mcfc.Range("A2", mcfc.Range("A2").End(xlDown)).Rows.Count + 1

MNCol = 2
FNCol = 4
FTNCol = 8

CVTRmyArr = Application.Transpose(Range(Cells(MainRowStart, MNCol), Cells(CVTRRowEnd, MNCol)))
FCmyArr = Application.Transpose(Range(Cells(MainRowStart, MNCol), Cells(FCRowEnd, MNCol)))
FNmyArr = Application.Transpose(Range(Cells(MainRowStart, FNCol), Cells(FCRowEnd, FNCol)))

mcfc.Activate    

For i = 2 To TR
    s = 0
    TCJ = Cells(1, Columns.Count).End(xlToLeft).Column + 7
    For j = 8 To TCJ
        TCK = mcfb.Cells(i, Columns.Count).End(xlToLeft).Column + 1
        For k = 2 To TCK
        XD = "=COUNTIFS(CVTRmyArr,'Sheet3'!R" & i & "C" & k & ",'Sheet3'!R[0]C" & k & ",FNmyArr,""*"" & SUBSTITUTE(MID(FNmyArr,FIND(""*"",SUBSTITUTE(FNmyArr,""("",""*"",LEN(FNmyArr) - LEN(SUBSTITUTE(FNmyArr,""("","""")))) +1,LEN(FNmyArr)),"")"",""""))"

        mcfc.Cells(i, j).Value = XD
        s = s + Cells(i, j).Value
        Cells(i, j).Value = s

        Next k
    Next j
Next i

End Sub

标签： arraysexcelexcel-formulareference