# Replacing Repeated Formula within Code. How??

#### monirg

##### Well-known Member
Hello;
I've a reference-contained formula repeated exactly in numerous VBA macros in the same code:
Code:
``````Sub Test_1()
' ...... code 1
Set myFormula = myRange.Offset(, myRange.Rows.Count).Rows(1)
myFormula.Formula = "=C21*\$D\$18^D21*\$E\$18^E21*\$F\$18^0.35*\$G\$18^G21/\$H\$18^H21*\$I\$18^I21"
' ...... code 11
End Sub

Sub Test_2()
' ...... code 2
Set myFormula = myRange.Offset(, myRange.Columns.Count).Columns(1)
myFormula.Formula = "=C21*\$D\$18^D21*\$E\$18^E21*\$F\$18^0.35*\$G\$18^G21/\$H\$18^H21*\$I\$18^I21"
' ...... code 22
End Sub
'etc``````
To change or modify the above formula, the code is searched and the same changes are made to the same formula everywhere!!
That's clearly not an efficient way, besides it does not preclude the possibility of missing some!
Can I place the formula statement, and only the formula statement:
Code:
``myFormula.Formula: = "=C21*\$D\$18^D21*\$E\$18^E21*\$F\$18^0.35*\$G\$18^G21/\$H\$18^H21*\$I\$18^I21"``
in a separate macro, and make reference to the macro in the corresponding procedures: Sub Test_1(), Sub Test_2(), ..., etc. ???
How ???
This way, changes/modifications to the formula could be made in one location only.

Thank you kindly for your help.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Lewiy

##### Well-known Member
Perhaps you could declare a global string variable and allocate your formula to it within the Workbook_Open event. You can then refer to this variable everywhere you want to place the formula and it will only exist in the Workbook_Open event to be modified.

#### pgc01

##### MrExcel MVP
Hi Monir

Another way is to define a Sub where you make the assignment, like:
Code:
``````Sub AssignFormula(rFormula As Range)
rFormula.Formula = "=C21*\$D\$18^D21*\$E\$18^E21*\$F\$18^0.35*\$G\$18^G21/\$H\$18^H21*\$I\$18^I21"
End Sub``````

This way you just call it from anywhere:
Code:
``````Sub Test_1()
' ...... code 1
Set myFormula = myRange.Offset(, myRange.Rows.Count).Rows(1)
Call AssignFormula(myFormula)
' ...... code 11
End Sub

Sub Test_2()
' ...... code 2
Set myFormula = myRange.Offset(, myRange.Columns.Count).Columns(1)
Call AssignFormula(myFormula)
' ...... code 22
End Sub``````

Hope this helps
PGC

#### Norie

##### Well-known Member
Monir

Why do you have 20 different subs with only a couple of lines of code?

#### monirg

##### Well-known Member
Hi;

Lewiy: For now, I'm trying to stay away from changing/modifying the existing Workbook_Open event. It was quite difficult to get working correctly!!

pgc01: I've just tried your suggestion by defining the formula in a Sub. It didn't work, producing a combination of #DIV/0!, 1.e+14, 0.00, #NUM!, and wrong results!
Shouldn't there be (in addition) some kind of a string declaration to the result of rFormula.Formula, in line with Lewiy's suggestion (but w/o the w/b open event ??

Norie: In all likelyhood, more code would've confused the issue, beside there're only 12 different Subs using the posted formula. (Did I mention 20 Subs earlier by mistake ?? My apologies)

Regards. #### Norie

##### Well-known Member
Monir

You didn't mention 20 subs, but in my old age my eyes multiply things by random numbers. But they seem to still let me see that you appear to repeating code with only minor changes, which might not be needed. #### monirg

##### Well-known Member
pgc01: You're correct and thank you for your suggestion.
Define a Sub where you make the assignment, like:
Code:
``````Sub AssignFormula(rFormula As Range)
rFormula.Formula = "=C21*\$D\$18^D21*\$E\$18^E21*\$F\$18^0.35*\$G\$18^G21/\$H\$18^H21*\$I\$18^I21"
End Sub``````
This way you just call it from anywhere:
Code:
``````Sub Test_1()
' ...... code 1
Set myFormula = myRange.Offset(, myRange.Rows.Count).Rows(1)
Call AssignFormula(myFormula)
' ...... code 11
End Sub

Sub Test_2()
' ...... code 2
Set myFormula = myRange.Offset(, myRange.Columns.Count).Columns(1)
Call AssignFormula(myFormula)
' ...... code 22
End Sub``````
It works fine, and it was my mistake! The problem was that one of the critical dollar sign (\$) in the formula had (mysteriously!) been left out in the process of copy/paste to the separate Sub. Because that part of the formula was not properly anchored, hence the erroneous results!

Norie: Yes, I agree! There's certainly some repetiton in the code, and I'll try later (hopefully with your help!) to minimize such repetition once I've the entire program working as desired.

Thank you all for your contributions in resolving the issue. Replies
3
Views
121
Replies
11
Views
240
Replies
1
Views
231
Replies
2
Views
81
Replies
8
Views
118

### Forum statistics

1,191,518
Messages
5,987,066
Members
440,074
Latest member
Emmanuelian ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back