Without writing specific code here is the general flow of the solution...

'On row 6 find the right most column...
Activesheet.range("A6").Specialcells(xlLeft).selec t

'Check to see if the number is 2000 if not move right and insert 2000
if Activecell.value < 2000 then
activecell.offset(0,-1).select
Activecell.value = 2000
endif

'loop until you find 1980
do while activecell.value 1980
'Now you can check the activecell againxt the cell to the left
if activecell.value - activecell.offset(0, -1).value < 0 then
activecell.entirerow.insert
endif
activecell.offset(0, -1).select
loop

or generally something like that give or take some debugging...


"dan graziano" wrote:

Hi,

I have an excel worksheet which has the following field headings for
sales data which start on row 6 of the worksheet:

company country 1980 1983 1984 1987 1988 etc.
Pepsi USA 100 200 333 224 298

The years start at column 3. In the final data set, there actually
should be a column and year field heading for each year from 1980 to
2000. However, in many worksheets the years included are irregular with
years skipped in-between such as above.

How would be the best way to macro automate so that the computer
automatically inserts a column and field heading whenever there is a
year missing to make a full series from 1980 to 2000?

For example, the above data set would be converted to:

company country 1980 1981 1982 1983 1984 etc. to 2000
Pepsi USA 100 200 333

Thanks and best regards,

Dan






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