Use Autofill in a macro to fill blank cells until next text - URGE

Desperately in need of some guidance.....

I'm creating a spreadsheet for a non-Excel end-user, thus trying to automate
the process almost entirely. Each week, they'll insert about 50 rows of
weekly raw data in five columns at the TOP of a
table using a macro i created based on InsertCopiedCells. However, in the
two columns to the right of the data there need to be two simple formulae
added after any new data is inserted - each week, slighty different.

So, for example, let's say our table consists of rows 10-450 and has five
columns of data (A:E) and two columns of formulae (F:G). Then User X inserts
45 more rows of data at the top. Now rows 10-55 have five columns of data,
but no formulae in columns 6 and 7. Starting in row 56, the rows have the
seven complete columns. I'm trying to write a macro that will highlight the
range F10:G55 (though the number of new rows each week will vary) and insert
the fomulae in the blanks - essentially performing Cntrl-Shift-Down -1.
***Note, I do have a hidden row above (row 9) that includes the formulae to
better enable autofill, if that's the best way.

Any ideas you have would be so much appreciated!!

THANK YOU SO MUCH!!!!!

Marika

Marika,

Range("F9:G9").AutoFill _
Destination:=Range("F9", Range("G9").End(xlDown)(0))

Is 1981 your birth year? That's the year I graduated from MIT....I feel
REALLY old now.....

HTH,
Bernie
MS Excel MVP

"marika1981" <marika1981@discussions.microsoft.com> wrote in message
news:E296472D-1ED5-436E-9815-FCD259512655@microsoft.com...
> Desperately in need of some guidance.....
>
> I'm creating a spreadsheet for a non-Excel end-user, thus trying to
automate
> the process almost entirely. Each week, they'll insert about 50 rows of
> weekly raw data in five columns at the TOP of a
> table using a macro i created based on InsertCopiedCells. However, in the
> two columns to the right of the data there need to be two simple formulae
> added after any new data is inserted - each week, slighty different.
>
> So, for example, let's say our table consists of rows 10-450 and has five
> columns of data (A:E) and two columns of formulae (F:G). Then User X
inserts
> 45 more rows of data at the top. Now rows 10-55 have five columns of
data,
> but no formulae in columns 6 and 7. Starting in row 56, the rows have the
> seven complete columns. I'm trying to write a macro that will highlight
the
> range F10:G55 (though the number of new rows each week will vary) and
insert
> the fomulae in the blanks - essentially performing Cntrl-Shift-Down -1.
> ***Note, I do have a hidden row above (row 9) that includes the formulae
to
> better enable autofill, if that's the best way.
>
> Any ideas you have would be so much appreciated!!
>
> THANK YOU SO MUCH!!!!!
>
> Marika
>

Sub AE()
Dim rng As Range, rng1 As Range
Set rng = Range("F9").End(xlDown).Offset(-1, 0)
Set rng1 = Range(Range("F9"), Cells(rng.Row, "G"))
Range("F9").Resize(1, 2).AutoFill Destination:=rng1

End Sub

--
Regards,
Tom Ogilvy



"marika1981" <marika1981@discussions.microsoft.com> wrote in message
news:E296472D-1ED5-436E-9815-FCD259512655@microsoft.com...
> Desperately in need of some guidance.....
>
> I'm creating a spreadsheet for a non-Excel end-user, thus trying to
automate
> the process almost entirely. Each week, they'll insert about 50 rows of
> weekly raw data in five columns at the TOP of a
> table using a macro i created based on InsertCopiedCells. However, in the
> two columns to the right of the data there need to be two simple formulae
> added after any new data is inserted - each week, slighty different.
>
> So, for example, let's say our table consists of rows 10-450 and has five
> columns of data (A:E) and two columns of formulae (F:G). Then User X
inserts
> 45 more rows of data at the top. Now rows 10-55 have five columns of
data,
> but no formulae in columns 6 and 7. Starting in row 56, the rows have the
> seven complete columns. I'm trying to write a macro that will highlight
the
> range F10:G55 (though the number of new rows each week will vary) and
insert
> the fomulae in the blanks - essentially performing Cntrl-Shift-Down -1.
> ***Note, I do have a hidden row above (row 9) that includes the formulae
to
> better enable autofill, if that's the best way.
>
> Any ideas you have would be so much appreciated!!
>
> THANK YOU SO MUCH!!!!!
>
> Marika
>

IT WORKED!!!! Oh my, I'm so excited!

Thank you! I was actually at MIT a couple months ago to see a presentation
on origami mathematics by an MIT CompSci professor, Erik Demaine, and a laser
physicist from CA. Great place!

Thanks again, so much

Marika




"Bernie Deitrick" wrote:

> Marika,
>
> Range("F9:G9").AutoFill _
> Destination:=Range("F9", Range("G9").End(xlDown)(0))
>
> Is 1981 your birth year? That's the year I graduated from MIT....I feel
> REALLY old now.....
>
> HTH,
> Bernie
> MS Excel MVP
>
> "marika1981" <marika1981@discussions.microsoft.com> wrote in message
> news:E296472D-1ED5-436E-9815-FCD259512655@microsoft.com...
> > Desperately in need of some guidance.....
> >
> > I'm creating a spreadsheet for a non-Excel end-user, thus trying to
> automate
> > the process almost entirely. Each week, they'll insert about 50 rows of
> > weekly raw data in five columns at the TOP of a
> > table using a macro i created based on InsertCopiedCells. However, in the
> > two columns to the right of the data there need to be two simple formulae
> > added after any new data is inserted - each week, slighty different.
> >
> > So, for example, let's say our table consists of rows 10-450 and has five
> > columns of data (A:E) and two columns of formulae (F:G). Then User X
> inserts
> > 45 more rows of data at the top. Now rows 10-55 have five columns of
> data,
> > but no formulae in columns 6 and 7. Starting in row 56, the rows have the
> > seven complete columns. I'm trying to write a macro that will highlight
> the
> > range F10:G55 (though the number of new rows each week will vary) and
> insert
> > the fomulae in the blanks - essentially performing Cntrl-Shift-Down -1.
> > ***Note, I do have a hidden row above (row 9) that includes the formulae
> to
> > better enable autofill, if that's the best way.
> >
> > Any ideas you have would be so much appreciated!!
> >
> > THANK YOU SO MUCH!!!!!
> >
> > Marika
> >
>
>
>

Marika,

There was a nice article on Professor Demaine in the New York Times on
Tuesday:
http://www.nytimes.com/2005/02/15/sc...agewanted=1&th

Free Registration required.

Bernie
MS Excel MVP

"marika1981" <marika1981@discussions.microsoft.com> wrote in message
news:02603FC1-CE07-4C70-B4EC-F7B681203282@microsoft.com...
> IT WORKED!!!! Oh my, I'm so excited!
>
> Thank you! I was actually at MIT a couple months ago to see a
presentation
> on origami mathematics by an MIT CompSci professor, Erik Demaine, and a
laser
> physicist from CA. Great place!

Bernie - looking for same aid here and hoping you can help

I started with a pivot table - copied and pasted values - and now i am
trying to fill in the blanks in the first 2 columns of what was my pivot
table so i can filter and sort on newly created columns alongside the pivot.

Is there a way to autofill until new text? Is it by column?

Thanks n advance.
goldy

"Bernie Deitrick" wrote:

> Marika,
>
> Range("F9:G9").AutoFill _
> Destination:=Range("F9", Range("G9").End(xlDown)(0))
>
> Is 1981 your birth year? That's the year I graduated from MIT....I feel
> REALLY old now.....
>
> HTH,
> Bernie
> MS Excel MVP
>
> "marika1981" <marika1981@discussions.microsoft.com> wrote in message
> news:E296472D-1ED5-436E-9815-FCD259512655@microsoft.com...
> > Desperately in need of some guidance.....
> >
> > I'm creating a spreadsheet for a non-Excel end-user, thus trying to
> automate
> > the process almost entirely. Each week, they'll insert about 50 rows of
> > weekly raw data in five columns at the TOP of a
> > table using a macro i created based on InsertCopiedCells. However, in the
> > two columns to the right of the data there need to be two simple formulae
> > added after any new data is inserted - each week, slighty different.
> >
> > So, for example, let's say our table consists of rows 10-450 and has five
> > columns of data (A:E) and two columns of formulae (F:G). Then User X
> inserts
> > 45 more rows of data at the top. Now rows 10-55 have five columns of
> data,
> > but no formulae in columns 6 and 7. Starting in row 56, the rows have the
> > seven complete columns. I'm trying to write a macro that will highlight
> the
> > range F10:G55 (though the number of new rows each week will vary) and
> insert
> > the fomulae in the blanks - essentially performing Cntrl-Shift-Down -1.
> > ***Note, I do have a hidden row above (row 9) that includes the formulae
> to
> > better enable autofill, if that's the best way.
> >
> > Any ideas you have would be so much appreciated!!
> >
> > THANK YOU SO MUCH!!!!!
> >
> > Marika
> >
>
>
>

Dim rng as Range, rng1 as Range
set rng = Columns(1).Resize(,2)
set rng1 = rng.SpecialCells(xlBlanks)
rng1.Formula = "=" & rng1(1).Offset(-1,0).Adress(0,0)
rng.Formula = rng.Value

--
Regards,
Tom Ogilvy


"goldyjk" <goldyjk@discussions.microsoft.com> wrote in message
news:C2CD57C7-8FBE-452B-9EA0-E442D96DBBBB@microsoft.com...
> Bernie - looking for same aid here and hoping you can help
>
> I started with a pivot table - copied and pasted values - and now i am
> trying to fill in the blanks in the first 2 columns of what was my pivot
> table so i can filter and sort on newly created columns alongside the
pivot.
>
> Is there a way to autofill until new text? Is it by column?
>
> Thanks n advance.
> goldy
>
> "Bernie Deitrick" wrote:
>
> > Marika,
> >
> > Range("F9:G9").AutoFill _
> > Destination:=Range("F9", Range("G9").End(xlDown)(0))
> >
> > Is 1981 your birth year? That's the year I graduated from MIT....I feel
> > REALLY old now.....
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "marika1981" <marika1981@discussions.microsoft.com> wrote in message
> > news:E296472D-1ED5-436E-9815-FCD259512655@microsoft.com...
> > > Desperately in need of some guidance.....
> > >
> > > I'm creating a spreadsheet for a non-Excel end-user, thus trying to
> > automate
> > > the process almost entirely. Each week, they'll insert about 50 rows
of
> > > weekly raw data in five columns at the TOP of a
> > > table using a macro i created based on InsertCopiedCells. However, in
the
> > > two columns to the right of the data there need to be two simple
formulae
> > > added after any new data is inserted - each week, slighty different.
> > >
> > > So, for example, let's say our table consists of rows 10-450 and has
five
> > > columns of data (A:E) and two columns of formulae (F:G). Then User X
> > inserts
> > > 45 more rows of data at the top. Now rows 10-55 have five columns of
> > data,
> > > but no formulae in columns 6 and 7. Starting in row 56, the rows have
the
> > > seven complete columns. I'm trying to write a macro that will
highlight
> > the
> > > range F10:G55 (though the number of new rows each week will vary) and
> > insert
> > > the fomulae in the blanks - essentially performing
Cntrl-Shift-Down -1.
> > > ***Note, I do have a hidden row above (row 9) that includes the
formulae
> > to
> > > better enable autofill, if that's the best way.
> > >
> > > Any ideas you have would be so much appreciated!!
> > >
> > > THANK YOU SO MUCH!!!!!
> > >
> > > Marika
> > >
> >
> >
> >