Help with a VBA Formula

[jsowa]

Hello,

I have a problem with excel. I've been trying to put a matrix multiplication formula into an excel macro, and cant help to think there's an easier way, but just starting to learn VBA, its a bit rough... Can someone help me with this?

Im trying to have the MMULT return the amount of cells in the Std. Deviation tab, and came up with a very long excel formula, but putting it into a macro said it was too long to run.

Can someone help with putting this to a VBA code?

Heres the formula
=IFERROR(MMULT(B4:B18,TRANSPOSE(B4:B18)),IFERROR(MMULT(B4:B17,TRANSPOSE(B4:B17)),IFERROR(MMULT(B4:B16,TRANSPOSE(B4:B16)),IFERROR(MMULT(B4:B15,TRANSPOSE(B4:B15)),IFERROR(MMULT(B4:B14,TRANSPOSE(B4:B14)),IFERROR(MMULT(B4:B13,TRANSPOSE(B4:B13)),IFERROR(MMULT(B4:B12,TRANSPOSE(B4:B12)),IFERROR(MMULT(B4:B11,TRANSPOSE(B4:B11)),IFERROR(MMULT(B4:B10,TRANSPOSE(B4:B10)),IFERROR(MMULT(B4:B9,TRANSPOSE(B4:B9)),IFERROR(MMULT(B4:B8,TRANSPOSE(B4:B8)),IFERROR(MMULT(B4:B7,TRANSPOSE(B4:B7)),IFERROR(MMULT(B4:B6,TRANSPOSE(B4:B6)),(MMULT(B4:B5,TRANSPOSE(B4:B5))))))))))))))))

 

[Eric W]

Welcome to the MrExcel forum.

Instead of that long formula, why not just:

=B4:B18*TRANSPOSE(B4:B18)

In my limited testing, I got the same results as your formula, except that instead of the #N/A's I got zeros. Then entering it via VBA is:

Range("E4:S18").FormulaArray = "=B4:B18*TRANSPOSE(B4:B18)"

You can preserve your original formula with something like:

=MMULT(OFFSET(B4,0,0,COUNT(B4:B18)),TRANSPOSE(OFFSET(B4,0,0,COUNT(B4:B18))))

 

[jsowa]

Welcome to the MrExcel forum.

Instead of that long formula, why not just:

=B4:B18*TRANSPOSE(B4:B18)

In my limited testing, I got the same results as your formula, except that instead of the #N/A's I got zeros. Then entering it via VBA is:

Range("E4:S18").FormulaArray = "=B4:B18*TRANSPOSE(B4:B18)"

You can preserve your original formula with something like:

=MMULT(OFFSET(B4,0,0,COUNT(B4:B18)),TRANSPOSE(OFFSET(B4,0,0,COUNT(B4:B18))))

Wow, thank you so much! It worked perfectly!

 

[Eric W]

Glad I could help!