各位先輩,小弟有點不會將列陣公式轉成VBA
想了很久試了很多次也不行求各位指點一下
還是我理解錯?
Dim myarr As Variant
jrow = Worksheets("calculate").Range("a3").CurrentRegion.Rows.Count
irow = Worksheets("Mandatory holiday").Range("a1").CurrentRegion.Rows.Count
For i2 = 3 To jrow
For i = 2 To irow
myarr = Worksheets("Mandatory holiday").Range("A2:B11")
'列陣公式是這樣的AQ3={SUM(COUNTIFS($G$2:$AK$2,'Mandatory holiday'!$A:$A,$G$3:$AK$3,'Mandatory holiday'!$B:$B))}
Worksheets("Calculate").Range("AQ" & i2) = Application.WorksheetFunction.Sum(Application.WorksheetFunction.CountIfs(Worksheets("calculate").Range("G2:AK2"), myarr(i, 1), Worksheets("calculate").Range("G" & i2 & ":AK" & i2), myarr(i, 2)))
Next
Next i2
End Sub
檔案連結
https://docs.google.com/spreadsheets/d/1iEEednAieuB3Pdh8HpGXfus5L5NQ998EYcwI22KwIlg/edit?usp=sharing