Hi,

I try to wright a command which autofills a range adjacent to information in
the cells left. So if Range A1:A300 contains data and I have a formule in cel
B1 I normally double click the fill-handle to copy (or autofill) the formula
down to cel B300 (which is adjacent to cel A300). The below statement helps
me manage to automatically autofill the range (B1:B300). My problem is that
range A can be variable, so this time it's up to cel A300, the following time
it's up to cel A500. How should i adjust the statement?!

Sub test()
'
' test Macro
' Macro recorded 19-03-2008 by dresses
'

'
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B316")
Range("B1:B316").Select
End Sub

various ways -

With ActiveSheet.Range("B1")
.AutoFill .Offset(0, -1).CurrentRegion.Columns(2)
End With

The above would only work correctly if A1 was in the top row of its
CurrentRegion, which in this case it must be as A1 is clearly the top row.

If that can't be guaranteed here's another way

Sub Test2
Dim rng As Range
Set rng = ActiveSheet.Range("B2")

rng.AutoFill Range(rng, rng.Offset(0, -1).End(xlDown).Offset(0, 1))

End Sub

BTW, no need to 'select' cells

Regards,
Peter T



"Basta1980" <(E-Mail Removed)> wrote in message
news:3DACB69C-D262-478C-9032-(E-Mail Removed)...
> Hi,
>
> I try to wright a command which autofills a range adjacent to information
in
> the cells left. So if Range A1:A300 contains data and I have a formule in
cel
> B1 I normally double click the fill-handle to copy (or autofill) the
formula
> down to cel B300 (which is adjacent to cel A300). The below statement
helps
> me manage to automatically autofill the range (B1:B300). My problem is
that
> range A can be variable, so this time it's up to cel A300, the following
time
> it's up to cel A500. How should i adjust the statement?!
>
> Sub test()
> '
> ' test Macro
> ' Macro recorded 19-03-2008 by dresses
> '
>
> '
> Range("B1").Select
> Selection.AutoFill Destination:=Range("B1:B316")
> Range("B1:B316").Select
> End Sub

Peter T,

Thnx for the tip. Sub Test 2 did the trick!

Regards

Basta1980

"Peter T" wrote:

> various ways -
>
> With ActiveSheet.Range("B1")
> .AutoFill .Offset(0, -1).CurrentRegion.Columns(2)
> End With
>
> The above would only work correctly if A1 was in the top row of its
> CurrentRegion, which in this case it must be as A1 is clearly the top row.
>
> If that can't be guaranteed here's another way
>
> Sub Test2
> Dim rng As Range
> Set rng = ActiveSheet.Range("B2")
>
> rng.AutoFill Range(rng, rng.Offset(0, -1).End(xlDown).Offset(0, 1))
>
> End Sub
>
> BTW, no need to 'select' cells
>
> Regards,
> Peter T
>
>
>
> "Basta1980" <(E-Mail Removed)> wrote in message
> news:3DACB69C-D262-478C-9032-(E-Mail Removed)...
> > Hi,
> >
> > I try to wright a command which autofills a range adjacent to information
> in
> > the cells left. So if Range A1:A300 contains data and I have a formule in
> cel
> > B1 I normally double click the fill-handle to copy (or autofill) the
> formula
> > down to cel B300 (which is adjacent to cel A300). The below statement
> helps
> > me manage to automatically autofill the range (B1:B300). My problem is
> that
> > range A can be variable, so this time it's up to cel A300, the following
> time
> > it's up to cel A500. How should i adjust the statement?!
> >
> > Sub test()
> > '
> > ' test Macro
> > ' Macro recorded 19-03-2008 by dresses
> > '
> >
> > '
> > Range("B1").Select
> > Selection.AutoFill Destination:=Range("B1:B316")
> > Range("B1:B316").Select
> > End Sub
>
>
>