MACRO TO REPEAT FORMULA

  • Thread starter Thread starter jay dean
  • Start date Start date
J

jay dean

I need a macro that will apply an array formula,say
= {SUM(if(....))}
across the Range E20:N20, and repeat the process after every 10 rows
all the way down to the end of the sheet.

Any assistance would be appreciated. Thanks!

Jay Dean.
 
Dim rng as Range, cell as Range
set rng = intersect(Range("E20,E30,E40,E50,E60,E70,E80,E90"). _
EntireRow,Columns(5).Resize(,10))

for each cell in rng
cell.FormulaArray = "some formula"
Next

or is it a multicell formula array

for each cell in Range("E20,E30,E40,E50,E60,E70,E80,E90")
cell.Resize(1,10).FormulaArray = "some formula"
Next
 
Tom-
The Macro ends at E90, but I would like for it to continue all the
way down the sheet. Is it possible?

Thanks!
Jay Dean
 
Sure, but there is no definition for all the way down the sheet.

for i = 20 to 65536 step 10
cells(i,"E").Resize(,10).FormulaArray = "some formula"
Next

Will fill up the worksheet.

set rng = Activesheet.UsedRange
lastrow = rng.Rows(rng.rows.count).row
for i = 20 to lastRow step 10
cells(i,"E").Resize(,10).FormulaArray = "some formula"
Next

goes through the last used row.
 
Back
Top