Using Excel 2000
I am trying to use autofill to copy a formula down but the range will vary.
M1 is the column header (Title)
M2 has the following formula in: =IF(L2="","ERROR","")
Column A always has a number in it for each row
The number of rows will vary
I have tried the following but it doesn't work - it fills M1 above it but
not the rows below it!
Dim howmany As Long
howmany = Application.CountA("a:a")
Set SourceRange = Range("M2:M2")
'Set fillRange = Range("M2:M19")
Set fillRange = Range("M2:M" & howmany)
SourceRange.AutoFill Destination:=fillRange
Range("M2").Select
Can someone let me know what I'm doing wrong.

Cheers, Dean.

Dean@ERYC wrote:
> Using Excel 2000
> I am trying to use autofill to copy a formula down but the range will vary.
> M1 is the column header (Title)
> M2 has the following formula in: =IF(L2="","ERROR","")
> Column A always has a number in it for each row
> The number of rows will vary
> I have tried the following but it doesn't work - it fills M1 above it but
> not the rows below it!
> Dim howmany As Long
> howmany = Application.CountA("a:a")
> Set SourceRange = Range("M2:M2")
> 'Set fillRange = Range("M2:M19")
> Set fillRange = Range("M2:M" & howmany)
> SourceRange.AutoFill Destination:=fillRange
> Range("M2").Select
> Can someone let me know what I'm doing wrong.
>
> Cheers, Dean.

Hi Dean,

try...

Dim howmany As Long
howmany = WorksheetFunction.CountA(Range("a:a"))
Set SourceRange = Range("M2")
Set fillRange = Range("M2:M" & howmany)
SourceRange.AutoFill Destination:=fillRange
Range("M2").Select

Ken Johnson

Thanks Ken thats sorted it.

Thank-you very much.

Dean

"Ken Johnson" wrote:

>
> Dean@ERYC wrote:
> > Using Excel 2000
> > I am trying to use autofill to copy a formula down but the range will vary.
> > M1 is the column header (Title)
> > M2 has the following formula in: =IF(L2="","ERROR","")
> > Column A always has a number in it for each row
> > The number of rows will vary
> > I have tried the following but it doesn't work - it fills M1 above it but
> > not the rows below it!
> > Dim howmany As Long
> > howmany = Application.CountA("a:a")
> > Set SourceRange = Range("M2:M2")
> > 'Set fillRange = Range("M2:M19")
> > Set fillRange = Range("M2:M" & howmany)
> > SourceRange.AutoFill Destination:=fillRange
> > Range("M2").Select
> > Can someone let me know what I'm doing wrong.
> >
> > Cheers, Dean.
>
> Hi Dean,
>
> try...
>
> Dim howmany As Long
> howmany = WorksheetFunction.CountA(Range("a:a"))
> Set SourceRange = Range("M2")
> Set fillRange = Range("M2:M" & howmany)
> SourceRange.AutoFill Destination:=fillRange
> Range("M2").Select
>
> Ken Johnson
>
>

Thanks Ken thats sorted it.

Thank-you very much.

Cheers, Dean

"Ken Johnson" wrote:

>
> Dean@ERYC wrote:
> > Using Excel 2000
> > I am trying to use autofill to copy a formula down but the range will vary.
> > M1 is the column header (Title)
> > M2 has the following formula in: =IF(L2="","ERROR","")
> > Column A always has a number in it for each row
> > The number of rows will vary
> > I have tried the following but it doesn't work - it fills M1 above it but
> > not the rows below it!
> > Dim howmany As Long
> > howmany = Application.CountA("a:a")
> > Set SourceRange = Range("M2:M2")
> > 'Set fillRange = Range("M2:M19")
> > Set fillRange = Range("M2:M" & howmany)
> > SourceRange.AutoFill Destination:=fillRange
> > Range("M2").Select
> > Can someone let me know what I'm doing wrong.
> >
> > Cheers, Dean.
>
> Hi Dean,
>
> try...
>
> Dim howmany As Long
> howmany = WorksheetFunction.CountA(Range("a:a"))
> Set SourceRange = Range("M2")
> Set fillRange = Range("M2:M" & howmany)
> SourceRange.AutoFill Destination:=fillRange
> Range("M2").Select
>
> Ken Johnson
>
>