Multiply the range using excel VBA

Question

I am using below code to pull data from sheet RawImport to PullData.

Dim lrA As Integer

    lrA = RawImport.Range("B" & Rows.Count).End(xlUp).Row

    PullData.Range("A2:A" & lrA - 6).Value = RawImport.Range("G8:G" & lrA).Value
    PullData.Range("A2:A" & lrA).NumberFormat = "d mmm yyyy h:mm;@"
    PullData.Range("A:A").Columns.AutoFit

    PullData.Range("B2:B" & lrA - 6).Value = RawImport.Range("E8:E" & lrA).Value

    PullData.Range("C2:C" & lrA - 6).Value = RawImport.Range("C8:C" & lrA).Value

    PullData.Range("D2:D" & lrA - 6).Value = RawImport.Range("D8:E" & lrA).Value

    PullData.Range("E2:E" & lrA - 6).Value = RawImport.Range("B8:B" & lrA).Value

    PullData.Range("F2:F" & lrA - 6).Value = RawImport.Range("F8:F" & lrA).Value

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

Now I would like to multiply E and F column (from Row 2 onwards) and add the result to G column. I tried to add below code along with above

PullData.Range("G2:G" & lrA - 6).Value = PullData.Range("F2:F" & lrA - 6).Value * PullData.Range("E2:E" & lrA - 6).Value

But I am getting Type Mismatch error while running the code. What will be the best way to multiply the values and assign to G column?

Don't use `Integer` [always use `Long`](https://stackoverflow.com/a/26409520/3219613) instead. Especially when dealing with row counts: Excel has more rows than `Integer` can handle. — Pᴇʜ, Sep 18 '17 at 13:50
You can't mulyiply two ranges on the way you are making it. Look at [this question](https://stackoverflow.com/questions/35639963/fastest-way-to-multiply-two-ranges) for more information on how to multiply two ranges — danieltakeshi, Sep 18 '17 at 13:50
That isn't what I asked. If it is anything other than **8** then you are going to get a Type Mismatch error due to the multiple maths operations. — , Sep 18 '17 at 13:51

score 3 · Accepted Answer · answered Sep 18 '17 at 13:48

Write a quick formula and resolve to the formula results.

PullData.Range("G2:G" & lrA - 6).Formula = "=F2*E2"
PullData.Range("G2:G" & lrA - 6) = PullData.Range("G2:G" & lrA - 6).Value

If you don't want to write the formula, create an array and loop through it storing the results from individual row multiplication operations.

score 2 · Answer 2 · answered Sep 18 '17 at 14:11

2

You could also use Autofill

PullData.Range("G2") = "=F2*E2"
PullData.Range("G2").Autofill Destination:=PullData.Range("G2:G" & lrA-6)

answered Sep 18 '17 at 14:11

danieltakeshi

887
9
37

This results in a formula expressing the multiplication operation, not raw values as the OP indicated. – Sep 18 '17 at 14:51

score 1 · Answer 3 · answered Sep 18 '17 at 13:49

1

Please use this code:

For i = 2 To lrA - 6
    PullData.Range("G" & i).Value = PullData.Range("F" & i).Value * PullData.Range("E" & i).Value
Next i

Hope this help.

answered Sep 18 '17 at 13:49

adhy wijaya

509
3
7

Multiply the range using excel VBA

3 Answers3