MACRO TO REPEAT FORMULA

[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.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

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

 

[jay dean]

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

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.