VBA Macro to Multiply Cells Across Sheets - Shares and Averages

Hello.
I have a macro problem.
I tried to write it in various ways, but nothing works, or he typed O or #NAME? Everywhere.
I would like to write a macro that in the Shares sheet in F: 2 to F: 180 will write the result of the multiplication, the first argument of which is from the Shares sheet from A: 2 to A: 180 (but it probably is well written with this RC [-5]) and the second is from the Averages sheet in cell A2 (i.e. from A2 Shares * A2 Average, A3 Shares * A2 Average, A4 Shares * A2 Average). Plus + multiplication of the argument from the worksheet Shares from B: 2 to B: 180 with the argument from the worksheet Averages in cell B2 (i.e. the same from B2 shares * from B2 average, B3 shares * B2 average, B4 shares * B2 average). And so on with cell C and D.

Here are my macros (which have an error somewhere, but I don't know where):

Sub Count_share ()

t = Sheets ("Averages"). Cells (2, 1) .Value
x = Sheets ("Average"). Cells (2, 2) .Value
y = Sheets ("Averages"). Cells (2, 3) .Value
z = Sheets ("Averages"). Cells (2, 4) .Value

Worksheets ("Shares"). Range ("F2: F180"). FormulaR1C1 = _
"= Shares! RC [-5] * t + Shares! RC [-4] * x + Shares! RC [-3] * y + Shares! RC [-2] * z"


End Sub

or

Sub Count_Share ()

t = Means! A2
x = Means! B2
y = Means! C2
Z = Medium! D2

Worksheets ("Shares"). Range ("F2: F180"). FormulaR1C1 = _
"= Shares! RC [-5] * t + Shares! RC [-4] * x + Shares! RC [-3] * y + Shares! RC [-2] * z"

End Sub


I tried instead of:
Worksheets ("Shares"). Range ("F2: F180"). FormulaR1C1 = _
"= Shares! RC [-5] * t + Shares! RC [-4] * x + Shares! RC [-3] * y + Shares! RC [-2] * z"


enter (without these variables t, x, y, z):

'"= Shares! RC [-5] * Shares! RC [-5] * Shares! RC [-4] * Shares! RC [-4] * Shares! RC [-3] * Shares! RC [-3] * Shares. (2, 3). + Shares! RC [-2] * Average Cells (2, 4) "


'"= Shares! RC [-5] * Averages! A2 + Shares! RC [-4] * Averages! B2 + Shares! RC [-3] * Averages! C2 + Shares! RC [-2] * Averages! D2 "
(in this case, he marks me in the formula in exel 'A2', 'B2' etc., he writes it to me in the formula, I don't know why.


Thanks in advance for your help.






End Sub

And have you tried to save it with normal functions ?? The description is quite intricate, but I don't think the macro is needed for this.
The macro doesn't work anyway, because the variable x or y are not recognized as part of the formula. Whenever you multiply by these variables, you have to "stick" their value to the formula:

Yes, I know you don't need a macro for this, but unfortunately I have a project to complete and macros are mandatory for all activities.
So I should understand that it should be like this:

Sub Count_share ()

t = Sheets ("Averages"). Cells (2, 1) .Value
x = Sheets ("Averages"). Cells (2, 2) .Value
y = Sheets ("Averages"). Cells (2, 3) .Value
z = Sheets ("Averages"). Cells (2, 4) .Value

Worksheets ("Shares"). Range ("F2: F180"). FormulaR1C1 = _
"= Shares! RC [-5] *" & t & "+ Shares! RC [-4] *" & x & "+ Shares! RC [-3] *" & y & "+ Shares! RC [-2 ]*" & WITH

End Sub

Because, unfortunately, I get the error Run-time error '1004' and mark me these lines "Worksheets (" shares ") ...... in yellow.

This is what it is supposed to be like.
And you have a worksheet called Average and in cells A2 2 you have entered the factors ??

Yes, everything is like that.

And, because you can see - you have coefficients as fractions, and I assumed integers ...
You must use a period instead of a comma to construct the function, so either replace all variables as: etc., or, instead of a constant value of the coefficients, refer directly to cells:

And one more question, because I get an error regarding this macro:

Sub Count_Covariance ()

Worksheets ("Covariance"). Range ("B2"). FormulaR1C1 = "= COVAR (Feet! A2: A180 / Feet! A2: A180)"

Worksheets ("Covariance"). Range ("B3, C2"). FormulaR1C1 = "= COVAR (Feet! A2: A180 / Feet! B2: B180)"

Worksheets ("Covariance"). Range ("B4, D2"). FormulaR1C1 = "= COVAR (Feet! A2: A180 / Feet! C2: C180)"

Worksheets ("Covariance"). Range ("B5, E2"). FormulaR1C1 = "= COVAR (Feet! A2: A180 / Feet! D2 180) "


Worksheets ("Covariance"). Range ("C3"). FormulaR1C1 = "= COVAR (Feet! B2: B180 / Feet! C2: C180)"

Worksheets ("Covariance"). Range ("C4, D3"). FormulaR1C1 = "= COVAR (Feet! B2: B180 / Feet! C2: C180)"

Worksheets ("Covariance"). Range ("C5, E3"). FormulaR1C1 = "= COVAR (Feet! B2: B180 / Feet! D2 180) "


Worksheets ("Covariance"). Range ("D4"). FormulaR1C1 = "= COVAR (Feet! C2: C180 / Feet! C2: C180)"

Worksheets ("Covariance"). Range ("D5, E4"). FormulaR1C1 = "= COVAR (Feet! C2: C180 / Feet! D2 180) "


Worksheets ("Covariance"). Range ("E5"). FormulaR1C1 = "= COVAR (Feet! D2 180 / Feet! D2 180) "

End Sub

How to write well covariance. Because I was doing using functions (massacre was so much to write, but unfortunately the project was not accepted, it's good that I have another chance) and you have to use a macro. I found that supposedly this function is called covar, so I don't know if it is well written. I would like to write a macro that creates the covariance of these feet from the "feet" worksheet and that it would write to me in a worksheet called "covariance".

How do you enter addresses in the format: A2, C180 etc. you can't save FormulaR1C1, only Formula. Instead of dividing, you enter a comma, that is:

Thank you very much.
The last question.

Why didn't it list all of them, just an error.
And in this cell E4, where he writes divide / 0 in the function field, he says that there is a covariance of rate D with rate E (as there is even no such rate) and is from 1 to 179, not from 2 to 180 as in the other cases.
Because the macro is entered correctly, so I don't know what's going on.

Show the code of this macro, which he writes data there.

Sub Licz_kowariancja()

Worksheets("Kowariancja").Range("B2").Formula = "=COVAR(Stopy!A2:A180, Stopy!A2:A180)"

Worksheets("Kowariancja").Range("B3,C2").Formula = "=COVAR(Stopy!A2:A180, Stopy!B2:B180)"

Worksheets("Kowariancja").Range("B4,D2").Formula = "=COVAR(Stopy!A2:A180, Stopy!C2:C180)"

Worksheets("Kowariancja").Range("B5,E2").Formula = "=COVAR(Stopy!A2:A180, Stopy!D2:D 180)"


Worksheets("Kowariancja").Range("C3").Formula = "=COVAR(Stopy!B2:B180, Stopy!C2:C180)"

Worksheets("Kowariancja").Range("C4,D3").Formula = "=COVAR(Stopy!B2:B180, Stopy!C2:C180)"

Worksheets("Kowariancja").Range("C5,E3").Formula = "=COVAR(Stopy!B2:B180, Stopy!D2:D 180)"


Worksheets("Kowariancja").Range("D4").Formula = "=COVAR(Stopy!C2:C180, Stopy!C2:C180)"

Worksheets("Kowariancja").Range("D5,E4").Formula = "=COVAR(Stopy!C2:C180, Stopy!D2:D 180)"


Worksheets("Kowariancja").Range("E5").Formula = "=COVAR(Stopy!D2:D 180, Stopy!D2:D 180)"

End Sub

If you want to insert the formula into several cells at the same time and not move the range, precede the number of the column and row with dollar signs in the addresses.

Yes, because something is not working out:

Sub Count_Covariance ()

Worksheets ("Covariance"). Range ("$ B $ 2"). Formula = "= COVAR (Feet! A2: A180, Feet! A2: A180)"

Worksheets ("Covariance"). Range ("$ B $ 3, $ C $ 2"). Formula = "= COVAR (Feet! A2: A180, Feet! B2: B180)"

Worksheets ("Covariance"). Range ("$ B $ 4, $ D $ 2"). Formula = "= COVAR (Feet! A2: A180, Feet! C2: C180)"

Worksheets ("Covariance"). Range ("$ B $ 5, $ E $ 2"). Formula = "= COVAR (Feet! A2: A180, Feet! D2 180) "


Worksheets ("Covariance"). Range ("$ C $ 3"). Formula = "= COVAR (Feet! B2: B180, Feet! C2: C180)"

Worksheets ("Covariance"). Range ("$ C $ 4, $ D $ 3"). Formula = "= COVAR (Feet! B2: B180, Feet! C2: C180)"

Worksheets ("Covariance"). Range ("$ C $ 5, $ E $ 3"). Formula = "= COVAR (Feet! B2: B180, Feet! D2 180) "


Worksheets ("Covariance"). Range ("$ D $ 4"). Formula = "= COVAR (Feet! C2: C180, Feet! C2: C180)"

Worksheets ("Covariance"). Range ("$ D $ 5, $ E $ 4"). Formula = "= COVAR (Feet! C2: C180, Feet! D2 180) "


Worksheets ("Covariance"). Range ("$ E $ 5"). Formula = "= COVAR (Feet! D2 180, Feet! D2 180) "

End Sub




But it comes out:
Sub Count_Covariance ()

Worksheets ("Covariance"). Range ("B2"). Formula = "= COVAR (Feet! A2: A180, Feet! A2: A180)"

Worksheets ("Covariance"). Range ("B3"). Formula = "= COVAR (Feet! A2: A180, Feet! B2: B180)"

Worksheets ("Covariance"). Range ("B4"). Formula = "= COVAR (Feet! A2: A180, Feet! C2: C180)"

Worksheets ("Covariance"). Range ("B5"). Formula = "= COVAR (Feet! A2: A180, Feet! D2 180) "


Worksheets ("Covariance"). Range ("C2"). Formula = "= COVAR (Feet! A2: A180, Feet! B2: B180)"

Worksheets ("Covariance"). Range ("C3"). Formula = "= COVAR (Feet! B2: B180, Feet! C2: C180)"

Worksheets ("Covariance"). Range ("C4"). Formula = "= COVAR (Feet! B2: B180, Feet! C2: C180)"

Worksheets ("Covariance"). Range ("C5"). Formula = "= COVAR (Feet! B2: B180, Feet! D2 180) "


Worksheets ("Covariance"). Range ("D2"). Formula = "= COVAR (Feet! A2: A180, Feet! C2: C180)"

Worksheets ("Covariance"). Range ("D3"). Formula = "= COVAR (Feet! B2: B180, Feet! C2: C180)"

Worksheets ("Covariance"). Range ("D4"). Formula = "= COVAR (Feet! C2: C180, Feet! C2: C180)"

Worksheets ("Covariance"). Range ("D5"). Formula = "= COVAR (Feet! C2: C180, Feet! D2 180) "



Worksheets ("Covariance"). Range ("E2"). Formula = "= COVAR (Feet! A2: A180, Feet! D2 180) "

Worksheets ("Covariance"). Range ("E3"). Formula = "= COVAR (Feet! B2: B180, Feet! D2 180) "

Worksheets ("Covariance"). Range ("E4"). Formula = "= COVAR (Feet! C2: C180, Feet! D2 180) "

Worksheets ("Covariance"). Range ("E5"). Formula = "= COVAR (Feet! D2 180, Feet! D2 180) "

End Sub



So I'll leave it as it comes out :)


THANKS SO MUCH FOR EVERYTHING !!!!!!!

Not at these addresses
With "feet" - I guess they taught you what dollars mean in function arguments ??

Yes, they did teach, but writing these macros for several hours today my brain doesn't even remember the basics anymore
And since I am writing this post, maybe this is the last, but the last question
What's wrong with this code, because I would like to, well, too much writing what I would like to do, but the general point is that I want to multiply:
covariance B2 * shares A2 * A2
+ covariance B3 * shares A2 * B2
+ covariance B4 * shares A2 * C2
+ covariance B5 * shares A2 * D2
+ C2 covariance * shares B2 * A2
+ covariance C3 * shares B2 * B2
+ covariance C4 * shares B2 * C2
+ C5 covariance * shares B2 * D2
+ covariance D2 * shares C2 * A2
+ covariance D3 * shares C2 * B2
+ covariance D4 * shares C2 * C2
+ covariance D5 * shares C2 * D2
+ covariance E2 * shares D2 * A2
+ covariance E3 * shares D2 * B2
+ covariance E4 * shares D2 * C2
+ covariance E5 * shares D2 * D2

By this writing I think I know what is wrong, because it should be Worksheets ("Shares"). Range ("G2"). FormulaR1C1
Yes?? I guess, and if so, then the loop is going to be entered from G2 to G180.
And then it will be For 2 To 180 Step 1
Is not a mistake at all

And I have this macro:
Sub Count_2 ()

Worksheets ("Shares"). Range ("G2: G180"). FormulaR1C1 = _
"= Covariance! RC [-5] * Shares! RC [-6] * Shares! RC [-6] _
+ Covariance! R [+1] C [-5] * Shares! RC [-6] * Shares! RC [-5] _
+ Covariance! R [+2] C [-5] * Shares! RC [-6] * Shares! RC [-4] _
+ Covariance! R [+3] C [-5] * Shares! RC [-6] * Shares! RC [-3] _
+ Covariance! RC [-4] * Shares! RC [-5] * Shares! RC [-6] _
+ Covariance! R [+1] C [-4] * Shares! RC [-5] * Shares! RC [-5] _
+ Covariance! R [+2] C [-4] * Shares! RC [-5] * Shares! RC [-4] _
+ Covariance! R [+3] C [-4] * Shares! RC [-5] * Shares! RC [-3] _
+ Covariance! RC [-3] * Shares! RC [-4] * Shares! RC [-6] _
+ Covariance! R [+1] C [-3] * Shares! RC [-4] * Shares! RC [-5] _
+ Covariance! R [+2] C [-3] * Shares! RC [-4] * Shares! RC [-4] _
+ Covariance! R [+3] C [-3] * Shares! RC [-4] * Shares! RC [-3] _
+ Covariance! RC [-2] * Shares! RC [-3] * Shares! RC [-6] _
+ Covariance! R [+1] C [-2] * Shares! RC [-3] * Shares! RC [-5] _
+ Covariance! R [+2] C [-2] * Shares! RC [-3] * Shares! RC [-4] _
+ Covariance! R [+3] C [-2] * Shares! RC [-3] * Shares! RC [-3] "

End Sub

But what's the question ??
Because when it comes to this formula, it is badly broken into lines. Lines start and end with quotation marks, and between the two you add & _
That is:

And you see, the quotation marks and & must be put in place, I wouldn't go to priesthood for anything, but I was looking for a mistake in my notes.
But there is probably some error in the notes, because with this "0" it does not suit me because it is dragging on to the end.



Added after 3 [minutes]:

Because there is covariance B27 in the formula field, and there is no such covariance. heh, that's the wrong formula

Added after 2 [minutes]:

It would be appropriate to write it without RC, only with cells, but if it writes like this, can there be RC in shares, and at the beginning as it is ....... Range ("G2: G180"). FormulaR1C1 it also has be R1C1 or not already. Can they not be so mixed that there is RC here and not there?

You multiply by A27, and there is 0. I don't know what you want to count, but check the formula components to see what the result is.

Formulas cannot be mixed - either you use R1C1 or regular notation.

If you write a formula and insert it into the cell as .FormulaR1C1, then in the formula you only use R (to refer to the current row) and C (to the current column) to specify addresses. For example, the formula R1C1 = Shares! RC inserted in VBA into cell F27 will be "translated" as = Shares! F27

Numbers typed directly next to R and C are absolute references (dollars), e.g. R3C4 means the reference to $ D $ 3, no matter which cell the formula is in. By entering the number next to R or C in square brackets, you indicate by how many rows or columns the address should be moved (plus or minus) relative to the current cell, i.e. for F27 = "= R [1] C" means F28, and "= R [- 7] C [-2] "is D20.

If you want to pass formulas using "normal" references - numbers and letters for columns - then you would use regular .Formula, eg you would write: Range ("A2"). Formula = "= Shares! F27". Then you can't use R and C and offsets in turn.

If you are inserting a formula into a range of cells from ... to, the formula you are inserting will apply only to the first cell in the range. The rest will be automatically moved by Excel as if you dragged them with the mouse - the row and column numbers will change accordingly - UNLESS you use the mentioned dollars (for Formula) or absolute references (for FormulaR1C1).

I know, but my point is that even in this figure, the function field says: = covariance! B27 .... and so on, but there is no such field in the covariance tab (ie it is blank).

Worksheets ("Shares"). Range ("G2: G180"). FormulaR1C1 = _
"= Covariance! B2 * Shares! RC [-6] * Shares! RC [-6]" & _
"+ Covariance! B3 * Shares! RC [-6] * Shares! RC [-5]" & _
"+ Covariance! B4 * Shares! RC [-6] * Shares! RC [-4]" & _
"+ Covariance! B5 * Shares! RC [-6] * Shares! RC [-3]" & _
"+ Covariance! C2 * Shares! RC [-5] * Shares! RC [-6]" & _
"+ Covariance! C3 * Shares! RC [-5] * Shares! RC [-5]" & _
"+ Covariance! C4 * Shares! RC [-5] * Shares! RC [-4]" & _
"+ Covariance! C5 * Shares! RC [-5] * Shares! RC [-3]" & _
"+ Covariance! D2 * Shares! RC [-4] * Shares! RC [-6]" & _
"+ Covariance! D3 * Shares! RC [-4] * Shares! RC [-5]" & _
"+ Covariance! D4 * Shares! RC [-4] * Shares! RC [-4]" & _
"+ Covariance! D5 * Shares! RC [-4] * Shares! RC [-3]" & _
"+ Covariance! E2 * Shares! RC [-3] * Shares! RC [-6]" & _
"+ Covariance! E3 * Shares! RC [-3] * Shares! RC [-5]" & _
"+ Covariance! E4 * Shares! RC [-3] * Shares! RC [-4]" & _
"+ Covariance! E5 * Shares! RC [-3] * Shares! RC [-3]"

This is wrong because it comes out #NAME?. But my point is not to write RC with covariance, because the covariance sheet is filled with numbers only from B2: E5 as in the picture.
But how to write this covariance now, if it is so wrong. Any quotation marks or something else ??
Can I write it differently and loop it ??



Added after 4 [minutes]:

So it has to be like this ??

Worksheets ("Shares"). Range ("G2: G180"). FormulaR1C1 = _
"= Covariance! R2C2 * Shares! RC [-6] * Shares! RC [-6]" & _
"+ Covariance! R3C2 * Shares! RC [-6] * Shares! RC [-5]" & _
"+ Covariance! R4C2 * Shares! RC [-6] * Shares! RC [-4]" & _
"+ Covariance! R5C2 * Shares! RC [-6] * Shares! RC [-3]" & _
"+ Covariance! R2C3 * Shares! RC [-5] * Shares! RC [-6]" & _
"+ Covariance! R3C3 * Shares! RC [-5] * Shares! RC [-5]" & _
"+ Covariance! R4C3 * Shares! RC [-5] * Shares! RC [-4]" & _
"+ Covariance! R5C3 * Shares! RC [-5] * Shares! RC [-3]" & _
"+ Covariance! R2C4 * Shares! RC [-4] * Shares! RC [-6]" & _
"+ Covariance! R3C4 * Shares! RC [-4] * Shares! RC [-5]" & _
"+ Covariance! R4C4 * Shares! RC [-4] * Shares! RC [-4]" & _
"+ Covariance! R5C4 * Shares! RC [-4] * Shares! RC [-3]" & _
"+ Covariance! R2C5 * Shares! RC [-3] * Shares! RC [-6]" & _
"+ Covariance! R3C5 * Shares! RC [-3] * Shares! RC [-5]" & _
"+ Covariance! R4C5 * Shares! RC [-3] * Shares! RC [-4]" & _
"+ Covariance! R5C5 * Shares! RC [-3] * Shares! RC [-3]"

Added after 2 [minutes]:

OKAY. It's already gone.
Thanks so much again.