AutoFill a Range

I have an Inputbox that asks for the number of couples attending an event.
If 50 is the value (stored in the variable NoCouples), and cell A1 already
contains "Couple01", I would like cell A1 to be autofilled down as follows:

Couple01
Couple02
Couple03
..
..
..
Couple50

Recording a macro that does this gives me the following line of code:
Selection.Autofill Destination:=Range("A1:A50"), Type:=xlFillDefault

How do I word my code to substitute NoCouples for A50? I've tried the
following to no avail:
Selection.Autofill Destination:=Range("A1:A + NoCouples"),
Type:=xlFillDefault

Thanks!

Steve C

Take the NoCouples outside the quotes to have VB replace the string with the
value:
Selection.Autofill Destination:=Range("A1:A" & NoCouples)


"Steve C" wrote:

> I have an Inputbox that asks for the number of couples attending an event.
> If 50 is the value (stored in the variable NoCouples), and cell A1 already
> contains "Couple01", I would like cell A1 to be autofilled down as follows:
>
> Couple01
> Couple02
> Couple03
> .
> .
> .
> Couple50
>
> Recording a macro that does this gives me the following line of code:
> Selection.Autofill Destination:=Range("A1:A50"), Type:=xlFillDefault
>
> How do I word my code to substitute NoCouples for A50? I've tried the
> following to no avail:
> Selection.Autofill Destination:=Range("A1:A + NoCouples"),
> Type:=xlFillDefault
>
> Thanks!
>
> Steve C

try this:
Selection.Autofill Destination:=Range("A1:A" & NoCouples),
Type:=xlFillDefault

"Steve C" <SteveC@discussions.microsoft.com> wrote in message
news:DB1DFAA4-05DF-4E3A-B2A1-D47E846ACE37@microsoft.com...
> I have an Inputbox that asks for the number of couples attending an event.
> If 50 is the value (stored in the variable NoCouples), and cell A1 already
> contains "Couple01", I would like cell A1 to be autofilled down as
follows:
>
> Couple01
> Couple02
> Couple03
> .
> .
> .
> Couple50
>
> Recording a macro that does this gives me the following line of code:
> Selection.Autofill Destination:=Range("A1:A50"),
Type:=xlFillDefault
>
> How do I word my code to substitute NoCouples for A50? I've tried the
> following to no avail:
> Selection.Autofill Destination:=Range("A1:A + NoCouples"),
> Type:=xlFillDefault
>
> Thanks!
>
> Steve C

Thank you both!

"Jef Gorbach" wrote:

>
> try this:
> Selection.Autofill Destination:=Range("A1:A" & NoCouples),
> Type:=xlFillDefault
>
> "Steve C" <SteveC@discussions.microsoft.com> wrote in message
> news:DB1DFAA4-05DF-4E3A-B2A1-D47E846ACE37@microsoft.com...
> > I have an Inputbox that asks for the number of couples attending an event.
> > If 50 is the value (stored in the variable NoCouples), and cell A1 already
> > contains "Couple01", I would like cell A1 to be autofilled down as
> follows:
> >
> > Couple01
> > Couple02
> > Couple03
> > .
> > .
> > .
> > Couple50
> >
> > Recording a macro that does this gives me the following line of code:
> > Selection.Autofill Destination:=Range("A1:A50"),
> Type:=xlFillDefault
> >
> > How do I word my code to substitute NoCouples for A50? I've tried the
> > following to no avail:
> > Selection.Autofill Destination:=Range("A1:A + NoCouples"),
> > Type:=xlFillDefault
> >
> > Thanks!
> >
> > Steve C
>
>
>