# Typed formula within macro

#### amkerchner

##### New Member
I would like to add a button on the first page of my workbook that does an update on a later sheet. I have found when I run the macro it will "paste" what ever the last thing I copied instead of the formula I would like it to paste. Because I am at a primitive stage in my macro writing I am still using the "record" feature and then editing the macro manually. Is there a way to type in the formula into the command so that each time I run the macro it will insert the same function into the cell/column?

The formula is as follows:

=IF(ISERROR(IF(AND(D2=D3,D3=D4,D4=D5),((I2*J2+I3*J3+I4*J4+I5*J5)/SUM(J2:J5)),(IF(AND(D2=D3,D3=D4),((I2*J2+I3*J3+I4*J4)/(SUM(J2:J4))),IF(D2=D3,((I2*J2+I3*J3)/(SUM(J2,J3))),I2))))),"",(IF(AND(D2=D3,D3=D4,D4=D5),((I2*J2+I3*J3+I4*J4+I5*J5)/SUM(J2:J5)),(IF(AND(D2=D3,D3=D4),((I2*J2+I3*J3+I4*J4)/(SUM(J2:J4))),IF(D2=D3,((I2*J2+I3*J3)/(SUM(J2,J3))),I2))))))

And I need it to run each time I click the button I've made. Any suggestions? I am attempting to take the weighted average of cost when there is a repeated number in column D.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### brian.wethington

##### Well-known Member
I have no idea if this is really this simple, but you can test something like this.

Code:
``````Sub FormulaMacro()

Range("F1").Formula = "=IF(ISERROR(IF(AND(D2=D3,D3=D4,D4=D5),((I2*J2+I3*J3+I4*J4+I5*J5)/SUM(J2:J5)),(IF(AND(D2=D3,D3=D4),((I2*J2+I3*J3+I4*J4)/(SUM(J2:J4))),IF(D2=D3,((I2*J2+I3*J3)/(SUM(J2,J3))),I2))))),"""",(IF(AND(D2=D3,D3=D4,D4=D5),((I2*J2+I3*J3+I4*J4+I5*J5)/SUM(J2:J5)),(IF(AND(D2=D3,D3=D4),((I2*J2+I3*J3+I4*J4)/(SUM(J2:J4))),IF(D2=D3,((I2*J2+I3*J3)/(SUM(J2,J3))),I2))))))"

End Sub``````
This I believe would put the formula into F1. Hope this is something like what you were looking to accomplish.

#### amkerchner

##### New Member
Yes, that worked. Thanks, I guess it WAS that simple #### Scott Huish

##### MrExcel MVP
That formula could probably be simplified if you could tell us what it is supposed to do.

Replies
0
Views
231
Replies
15
Views
445
Replies
5
Views
145
Replies
1
Views
227
Replies
3
Views
167

### Forum statistics

1,191,043
Messages
5,984,315
Members
439,882
Latest member
gerdc ### 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