Is it possible to paste something without first copying

[wroze27]

Background:
I want to clear up an excel document for my boss and there is a formula that gets copied and pasted into many other cells across the document. Right now I have a block of cells marked off with the formula inside to use as a reference to copy and paste. I think the sheet would look cleaner if I could remove that reference and just have the formula pasted from the VBA.
My question: Is there a way to paste something without needing to copy first? Can you just write the cell's text/formula that will get pasted in the code itself?

Thanks for your help, I'm new to this side of Excel and getting better everyday

 

[jasonb75]

You can use something on the lines of Worksheets("Sheet1").Range("A1:B10").Formula ="your formula" to enter "your formula" into the sheet and range specified, however it is not quite so simple in reality. There are differences in the way that the formula needs to be written. Any text double quotes in the formula need to be duplicated, for example "text" becomes ""text"". If your formula uses relative references that need to adjust based on where it is entered then you will need to use FormulaR1C1 with R1C1 references.

There may be other things to take into consideration depending on the actual formula(s).

 

[wroze27]

You can use something on the lines of Worksheets("Sheet1").Range("A1:B10").Formula ="your formula" to enter "your formula" into the sheet and range specified, however it is not quite so simple in reality. There are differences in the way that the formula needs to be written. Any text double quotes in the formula need to be duplicated, for example "text" becomes ""text"". If your formula uses relative references that need to adjust based on where it is entered then you will need to use FormulaR1C1 with R1C1 references.

There may be other things to take into consideration depending on the actual formula(s).

Can you explain to me why Worksheets("Sheet1").Range("R5").FormulaR1C1 = "=IF(R[0]C[-2]="""","""",R[0]C[-15]))" [/CODE] isn't working?

 

[Rick Rothstein]

It looks like you have an extra closing parenthesis at the end of your formula.