I have talked about this same report in another message (see "Excel VBA -
Compressing a Workbook"), I have a different question this time. In my daily
report that I am running, without going into too much detail, the report
breaks down different business, and portfolios within each business. Within
each of these businesses, the numbers have to show in each row under that
portfolio what they are for each business day in the month up to the current
day (i.e. 11/30 shows 11/1 - 11/30 so it would have 20 rows (20 business
days)). The column detail for each row spans from Column A - Column IC...
yes that was not my decision, and unfortunately one that I can't change (and
I have to question myself how much of it is actually viewed, but that's an
entirely different topic)...

So my process in Access/VBA that writes to Excel looks a little something
like this in a high level view:
For each business in the listing, query on the sums of the breakouts of the
dollar amount for each portfolio/day.
Write those values out to the current row in excel based on a mapping table
I made to map a field to an excel column.
Then I have another table I run which maps the formulas to the columns (i.e.
one entry might have ColumnName "IB" and Formula "=IF(ISERROR(HV%ROW% -
HP%ROW%),0,HV%ROW% - HP%ROW%)". I replace that %ROW% with whatever the
current row is on that sheet. I might want to mention here, that there is a
special sheet because the columns would have expanded past the 255 column
limit, that contains this extra breakout for every piece of data in question,
so the rows on that sheet don't perfectly correllate to the rows on other
sheets (and they rely on the "Total Outstanding" data on the original sheets
to get percentages, so row 300 on that special sheet may correllate to row 20
on the original sheet). Obviously that's poor design, and it would have been
better to break out another tab for each business sheet tab... but you know
how it goes.

I have turned off automatic calculation and screen updating within my code,
and I have also tried to run through the queries to see if they are causing
the bottleneck in my process and they are not. The bottleneck is when I am
writing the formulas out to the columns for each row, which takes about a
second for each day in each portfolio in each business (so the total process
takes about 31 minutes to run I've clocked it at).

So, my question here is simple, is there a way to speed up this lengthy
formula writing so that the process runs more efficiently? Is there a way to
write a series of varying formulas to a range all at one time to speed up the
process?