I've just started learning Excel 2000.
I have to create a workbook for projected sales for 2004.
One dimension will be each day in the fiscal year, the
other will be restaurant units.
I would have liked the columns to be the days but ran into
the 255 max column limitation, so I made the rows the days
and the columns the units.
The workbook needs to be a template that can be loaded by
different divisions that run different restaurants. The
days will be preset in the template but the units will be
filled in by the division (thus I don't know how many
columns there will be).

I will need a macro that does the following:
Reads all the data for the worksheet and creates a new
worksheet.
The new worksheet will contain the same rows and columns
but instead of the sales figures will contain a percentage
such that for each unit the percentages add up to 100.
This figure will be the percentage of yearly sales
attributed to each day.
How do I code this macro and how would I run it?

In particular, how do I refer to rows and columns, how do
I total each units sales to figure the percentage
(remember I dont know how many units there will be), and
how do I load all this into the new worksheet?

Any help appreciated to get me started.
- David

Dim rng as Range, cell as Range
Dim dblTotal as Double
Activesheet.copy After:=ActiveSheet
set rng = Range("A1").CurrentRegion
' Assume first column and first row are headers
set rng = rng.offset(1,1).Resize(rng.rows.count=1,rng.column s.count-1)
for each col in rng.columns
dblTotal = Application.Sum(col)
if dblTotal < 0 then
for each cell in col.Cells
cell.Value = cell.Value/dblTotal
cell.Numberformat = "0.0%"
Next
Next

--
Regards,
Tom Ogilvy


"David" wrote in message
...
I've just started learning Excel 2000.
I have to create a workbook for projected sales for 2004.
One dimension will be each day in the fiscal year, the
other will be restaurant units.
I would have liked the columns to be the days but ran into
the 255 max column limitation, so I made the rows the days
and the columns the units.
The workbook needs to be a template that can be loaded by
different divisions that run different restaurants. The
days will be preset in the template but the units will be
filled in by the division (thus I don't know how many
columns there will be).

I will need a macro that does the following:
Reads all the data for the worksheet and creates a new
worksheet.
The new worksheet will contain the same rows and columns
but instead of the sales figures will contain a percentage
such that for each unit the percentages add up to 100.
This figure will be the percentage of yearly sales
attributed to each day.
How do I code this macro and how would I run it?

In particular, how do I refer to rows and columns, how do
I total each units sales to figure the percentage
(remember I dont know how many units there will be), and
how do I load all this into the new worksheet?

Any help appreciated to get me started.
- David

ok, I figured out the 'end if' is missing; you also had
an '=' instead of an '-' in the second set statement.
It now works fine. Thanks!
- David

-----Original Message-----
Thanks for this code, however, I get 'next without for'
error on the second 'next'.
- David
-----Original Message-----
Dim rng as Range, cell as Range
Dim dblTotal as Double
Activesheet.copy After:=ActiveSheet
set rng = Range("A1").CurrentRegion
' Assume first column and first row are headers
set rng = rng.offset(1,1).Resize
(rng.rows.count=1,rng.columns.count-1)
for each col in rng.columns
dblTotal = Application.Sum(col)
if dblTotal < 0 then
for each cell in col.Cells
cell.Value = cell.Value/dblTotal
cell.Numberformat = "0.0%"
Next
Next

--
Regards,
Tom Ogilvy


"David" wrote in message
...
I've just started learning Excel 2000.
I have to create a workbook for projected sales for
2004.
One dimension will be each day in the fiscal year, the
other will be restaurant units.
I would have liked the columns to be the days but ran
into
the 255 max column limitation, so I made the rows the
days
and the columns the units.
The workbook needs to be a template that can be loaded
by
different divisions that run different restaurants. The
days will be preset in the template but the units will
be
filled in by the division (thus I don't know how many
columns there will be).

I will need a macro that does the following:
Reads all the data for the worksheet and creates a new
worksheet.
The new worksheet will contain the same rows and
columns
but instead of the sales figures will contain a
percentage
such that for each unit the percentages add up to 100.
This figure will be the percentage of yearly sales
attributed to each day.
How do I code this macro and how would I run it?

In particular, how do I refer to rows and columns, how
do
I total each units sales to figure the percentage
(remember I dont know how many units there will be),
and
how do I load all this into the new worksheet?

Any help appreciated to get me started.
- David



.

.

How do I give the new worksheet created a name?
- David


-----Original Message-----
Dim rng as Range, cell as Range
Dim dblTotal as Double
Activesheet.copy After:=ActiveSheet
set rng = Range("A1").CurrentRegion
' Assume first column and first row are headers
set rng = rng.offset(1,1).Resize
(rng.rows.count=1,rng.columns.count-1)
for each col in rng.columns
dblTotal = Application.Sum(col)
if dblTotal < 0 then
for each cell in col.Cells
cell.Value = cell.Value/dblTotal
cell.Numberformat = "0.0%"
Next
Next

--
Regards,
Tom Ogilvy


"David" wrote in message
...
I've just started learning Excel 2000.
I have to create a workbook for projected sales for
2004.
One dimension will be each day in the fiscal year, the
other will be restaurant units.
I would have liked the columns to be the days but ran
into
the 255 max column limitation, so I made the rows the
days
and the columns the units.
The workbook needs to be a template that can be loaded
by
different divisions that run different restaurants. The
days will be preset in the template but the units will
be
filled in by the division (thus I don't know how many
columns there will be).

I will need a macro that does the following:
Reads all the data for the worksheet and creates a new
worksheet.
The new worksheet will contain the same rows and columns
but instead of the sales figures will contain a
percentage
such that for each unit the percentages add up to 100.
This figure will be the percentage of yearly sales
attributed to each day.
How do I code this macro and how would I run it?

In particular, how do I refer to rows and columns, how
do
I total each units sales to figure the percentage
(remember I dont know how many units there will be), and
how do I load all this into the new worksheet?

Any help appreciated to get me started.
- David



.