使用mmult时，在VBA中键入不匹配错误

I am trying to code this forumla in vba.

我想在vba中编写这个论坛。

=MMULT(TRANSPOSE(F124:F370-L124),G124:G370-L125)/246

The code in vba I written looks like this:

我写的vba中的代码如下所示：

Sheets("1.A").Cells(124, 14) = Application.WorksheetFunction.MMult(Application.WorksheetFunction.Transpose(Worksheets("1.A").Range("F" & matchStartRow & ":F" & matchEndRow) - Cells(124, 12)), Worksheets("1.A").Range("G" & matchStartRow & ":G" & matchEndRow) - Cells(125, 12)) / (matchEndRow - matchStartRow)

But it is giving me a type mismatch error. Not sure where it is coming from

但它给了我一个类型不匹配的错误。不知道它来自哪里

1 个解决方案

#1

It is much easier to use EVALUATE either directly as

直接使用EVALUATE要容易得多

Sheets("1.A").Cells(124, 14) = Evaluate("=MMULT(TRANSPOSE(F124:F370-L124),G124:G370-L125)/246")

or with your variables

或者你的变量

Dim strEval As String
matchStartRow = 124
matchEndRow = 370
strEval = "=MMULT(TRANSPOSE(F" & matchStartRow & ":F" & matchEndRow & "-L124),G" & matchStartRow & ":G" & matchEndRow & "-L125)/(" & matchEndRow & " - " & matchStartRow & ")"
Sheets("1.A").Cells(124, 14) = Evaluate(strEval)

#1