CODEWITHSUNDEEP
vbaexcelexcel-formula
Georgios
Georgios

Reputation: 1037

VBA - TRANSPOSE in SUMPRODUCT

I want to calculate the scalar product of two vectors in VBA.

Since Excel has a function "SUMPRODUCT" that does this, I am trying to use it within VBA.

To that I want to transpose one of the two vectors, without wre-writing the whole vector in a Worksheet.

Is this possible in VBA?

Here is an example of mine that is not working at the moment:

X = Worksheets("Tabelle1").Range("A1:C1").Value2
XT = Application.Transpose(X)

Worksheets("Tabelle1").Cells(1, 11).Formula = _
        "=SUMPRODUCT(XT,G1:G3)"

Upvotes: 0

Views: 363

Answers (1)

Lisa
Lisa

Reputation: 552

I would suggest transposing the vector A1:C1 within the formula. And we need to use .FormulaArray instead of .Formula. Try the following to replace your code example.

Worksheets("Tabelle1").Cells(1, 11).FormulaArray = _
            "=SUMPRODUCT(TRANSPOSE(A1:C1),G1:G3)"

Upvotes: 0

Related Questions