How to multiply cells in one excel sheet with multiple cells in another excel sheet

Question

I have a workbook with two excel sheets: sheet1 and sheet2. In sheet1 I have a large amount of data from column B to CE.

Using VBA I am currently copying each row from Sheet1 in the range "B3:AP3" to Sheet2 duplicating each line so it will be represented twice. This is working fine!

The problem i have as that in Sheet2 I now need to make a calculation. In Sheet1 I have a number in cell CE and CF for each row. As these rows have been duplicated I need to multiply the number in cell AP3 of Sheet2 with that of CE3 in Sheet1 and that of AP4 in Sheet2 with that of CF3 in Sheet1.

The macro needs to keep on doing this with the subsequent cells. In total cell AP the first duplicated line needs to be multiplied with the number CE3 from Sheet1 and cell AP of the second duplicated line needs to be multiplied with the number CF3 from sheet1. The result should be posted in Sheet2 column AQ.

Below I have posted the code for duplicating and moving the lines but I am really struggling with the multiplications, any help is greatly appreciated!

Sub CopyRows()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Long, k As Long
Dim ws1LR As Long, ws2LR As Long

Set ws1 = Sheets("Bearbejdning")
Set ws2 = Sheets("Sheet8")

ws1LR = ws1.Range("B" & Rows.Count).End(xlUp).Row + 1
ws2LR = ws2.Range("B" & Rows.Count).End(xlUp).Row + 1

i = 2
k = ws2LR
Do Until i = ws1LR
    With ws1
        .Range(.Cells(i, 1), .Cells(i, 43)).Copy
    End With

    With ws2
        .Cells(k, 1).PasteSpecial
        .Cells(k, 1).Offset(1, 0).PasteSpecial
    End With

    Range("k,AR" & ws2LR).Formula = "=ws1.range(.Cells(i, 73)) * ws2.range(.Cells(k, 36))"

    k = k + 2
    i = i + 1
Loop

End Sub

RubberDuck RubberDuck · Accepted Answer · 2014-05-26T12:24:16

You can't pass an object into an excel formula that way.

Try this:

Range(k,"AR" & ws2LR).Formula = "=" & ws1.range(.Cells(i, 73)).address &" * " & ws2.range(.Cells(k, 36)).address

Alternatively, you could place the pre-calculated value in the cell.

Range(k,"AR" & ws2LR).Value = ws1.range(.Cells(i, 73)) * ws2.range(.Cells(k, 36))

Also, this Range call looks strange to me: Range("k,AR"). I think it should be something like Cells(k,"AR").

Variables shouldn't be inside quotes and Range doesn't accept Row/Column parameters.

How to multiply cells in one excel sheet with multiple cells in another excel sheet

1 Answers