New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

repeating excel formulas

Macros, VBA, Excel Automation, etc.

repeating excel formulas

Postby BJHOME2STAY » Wed Dec 14, 2011 11:48 pm

is there any way to repeat a cell's formula other than clicking on the cell's corner and drag ?

=SUM(L14;M14)
=SUM(L15;M15)
=SUM(L16;M16)
=SUM(L18;M18)
... and more 3.000 times....
  • 0

BJHOME2STAY
Rookie
 
Posts: 1
Joined: Dec 14, 2011
Reputation: 0

Re: repeating excel formulas

Postby Sisyphus » Fri Dec 16, 2011 3:36 am

Hi,

This macro will do the job:

Code: Select all
Range("A1").Copy Range("A3:A10")

Open the VB Editor (Alt F11).
If it isn't displayed, display the Immediate Window (View/Immediate Window).
Paste the code in this window and enter return.
Done.

A1 is where the original formula is. You can change this address.
A3:A10 is the range where you want to copy to. You can change this range to anything you want.
Just be careful! The action will be taken on the "Active" Sheet which is the last sheet you looked at before you changed to the VBE window, and it could be in a different workbook than you intended. The action can't be undone.

Have a cup of coffee, now that you saved so much time! :D
Regards,
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: repeating excel formulas

Postby User_5 » Fri Dec 16, 2011 4:05 am

HaHa! I was working to answer this quetion at the sam time and here's what I came up with:
I assume you want to have the resulsts in column N.
Put values in columns L and M to see the results in column N.

Code: Select all
Sub FillFormulas()
  With ActiveSheet
    Range("N14").Formula = "=SUM(L14:M14)"
    Range("N14").AutoFill Range("N14:N3500")
  End With
End Sub


I like Sisyphus's solution but the're both very fast.
  • 0

User_5
Regular
 
Posts: 34
Joined: Dec 13, 2011
Reputation: 0


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 41 guests

cron