CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
acr
acr

Reputation: 1746

Multiply the range using excel VBA

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?

Upvotes: 3

Views: 1787

Answers (3)

danieltakeshi
danieltakeshi

Reputation: 939

You could also use Autofill

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

Upvotes: 2

adhy wijaya
adhy wijaya

Reputation: 509

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.

Upvotes: 1

user4039065
user4039065

Reputation:

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.

Upvotes: 3

Related Questions