Help with offset for pasting formulas

[mlibrett]

I have a formula in one cell, and I need to write the code so that it copies and pastes this formula into the 8 cells beneath it without specifying the actual cells. I'm assuming some sort of offset code would work but I don't know where to begin or find this.

Thanks!

 

[njimack]

Welcome to the board. Something like this:

Sub copy()
Application.ScreenUpdating = False
With Range("A1") 'change cell reference as required
    .copy Range(.Offset(1), .Offset(8))
End With
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

I have a formula in one cell, and I need to write the code so that it copies and pastes this formula into the 8 cells beneath it without specifying the actual cells. I'm assuming some sort of offset code would work but I don't know where to begin or find this.

You didn't say which cell, so I used the ActiveCell (change the two references to it to the cell reference you want)... this single line of code should do what you described you want.

ActiveCell.Resize(9).Formula = ActiveCell.Formula

Note the use of 9 is correct as it is the ActiveCell plus the 8 cells underneath it for a total of 9 cells.

 

[mlibrett]

Thanks for the help! I'll try messing around with those now. What does Application Updating do?

 

[mlibrett]

That worked, thanks a lot!

 

[Rick Rothstein]

Thanks for the help! I'll try messing around with those now. What does Application Updating do?

When you set Application.ScreenUpdating to False, all screen display activity (within Excel) is halted, so any anything you code puts into cells won't display until you turn ScreenUpdating back on (setting it to True)... doing this helps speed up code. It is more useful in large looping code. I have found that for the number of cells most people work with, multi-cell range copying and multi-cell direct range assignments usually do not need this assist... I would say it is really not necessary for copying a total of eight cells.

 

[Rick Rothstein]

That worked, thanks a lot!

I'm not sure which one you were referring to when you said "that worked", but just so you know, both njimack's and my code will do what you asked... it is just two different methods to achieve the same end goal.

 

[mlibrett]

ActiveCell.Resize(9).Formula for this one, do I have to put the formula myself or it will know to take the formula in the active cell?

 

[Rick Rothstein]

ActiveCell.Resize(9).Formula for this one, do I have to put the formula myself or it will know to take the formula in the active cell?

Use the code line exactly as I posted it... it will automatically pull the formula from the active cell and copy it back into itself while at the same time copy it into the eight cells underneath it.

 

[mlibrett]

Great I really appreciate your help