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?
thank you in advance,
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,
Thanks for your answers.
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
by
and the results are given in
. 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
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:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("c5")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = ActiveWorksheet
Set s2 = Sheets("Report")
Dim i As Long, j As Long
For i = 4 To 14
s1.Range(Cells(20, i)).Value = s2.Range(Cells(73, i + 1)) * s1.Range("C5")
Next i
End If
End SubRe: 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
I am guessing that you are attempting to run the code from the "Report" worksheet and not the other one. When I originally wrote this, there was no mention that it would appear in a worksheet change event.
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
Did you correct my typo that Stephen pointed out in post #5?
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
Don’t have an account yet? Register yourself now and be a part of our community!