 # Multiply each cell in a range with a cell in another worksheet

• I am trying to figure out how to multiply each individual cell in a range, which is in another worksheet (e73:n73 of the sheet "Report"), by the value in "C5" of the active sheet and the results will be shown again in the active sheet, in d20:n20, however, I havent succeeded in it yet.

Is there a way to do this in vba - as there are already data in the range d20:n20 and they are re-calculated when the cell value "c5" is changed?

Selc

• Re: Multiply each cell in a range with a cell in another worksheet

Try this:

• Re: Multiply each cell in a range with a cell in another worksheet

Presumably you mean E20 rather than D20? Do you mean the data in D20-N20 should be overwritten? This might do what you want

• Re: Multiply each cell in a range with a cell in another worksheet

Hi Alan and Stephen,

Alan your answer seems like the answer I've been trying to figure out for some time now, but it gives 424, Object required error. can it be because you define a "j" but it then it is not used in the code afterwards?

Stephen, it is the multiplication of

Code
``worksheets("report").range("e73:n73")``

by

Code
``Worksheets(Output).range("c5")``

and the results are given in

Code
``Worksheets(Output).range("d20:m20")``

. But you are right, i should have written d20:m20, instead. It is my mistake and I apologize for it.

• Re: Multiply each cell in a range with a cell in another worksheet

Selc - just a typo in Alan's code, should be

Code
``Set s1 = Activesheet``
• Re: Multiply each cell in a range with a cell in another worksheet

Hi again Stephen,

Now the macro stops at the formula and gives "range of object _worksheet failed".
Have you any idea why this might be happening?

• Re: Multiply each cell in a range with a cell in another worksheet

in case it helps, below is the code:

• Re: Multiply each cell in a range with a cell in another worksheet

When you get the error message, click on the debug button and advise us which line of code is highlighted. Also, you can deleted the j as long from the dimensions as I never used it as originally anticipated.

Stephen--thanks for picking up the typo.

• Re: Multiply each cell in a range with a cell in another worksheet

It is the below line which is highlighted,

Code
``s1.Range(Cells(20, i)).Value = s2.Range(Cells(73, i + 1)) * s1.Range("C5")``

Thanks,
/s

• Re: Multiply each cell in a range with a cell in another worksheet

Worksheet change event is to trigger this code - so i didn't expect that it would be relevant to what I am actually trying to achieve - but i can see that i could have mentioned that earlier.
Btw, I am running it from the "Output" sheet.

/s

• Re: Multiply each cell in a range with a cell in another worksheet

Yes, I did. It helped removing with the "variable not defined" error which was popping up where you define active worksheet as "s1". I am no longer having that one, but the one with the Method 'Range' of object '_worksheet' failed error.

• Re: Multiply each cell in a range with a cell in another worksheet

Thanks Alan.
That one solved the problem.

/s

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!