Fastest way to multiply two ranges

Question

I have two ranges in Excel of the same size, and I want to multiply them in another range using vba. For example: Range 1: Sheets("A").Range("A1:H200") Range 2: Sheets("B").Range("A1:H200") Then I need:

Sheets("C").Range("A1:H200")= Range 1 * Range 2

That is, I need each cell of Sheet C to be the multiplication of the same cell in Sheets A and B.

Answer 1

Perhaps the fastest is to use a Sub to manipulate the cell formula in C.

Try this, you get the idea...

Option Explicit

Sub FillFormula()
    Worksheets("C").Range("A1:H200").FormulaR1C1 = "=A!RC*B!RC"
End Sub

Answer 2

This will do it nearly instantly:

Sheets("C").Range("A1:H200") = [INDEX(A!A1:H200*B!A1:H200,0)]