Typed formula within macro

[amkerchner]

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.

 

[brian.wethington]

I have no idea if this is really this simple, but you can test something like this.

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]

Yes, that worked. Thanks, I guess it WAS that simple

 

[Scott Huish]

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