AutoFill down a varying Range

[Dean@ERYC]

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.

 

[Ken Johnson]

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

 

[Dean@ERYC]

Thanks Ken thats sorted it.

Thank-you very much.

Dean

 

[Dean@ERYC]

Thanks Ken thats sorted it.

Thank-you very much.

Cheers, Dean