AutoFill down a varying Range

D

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.
 
K

Ken Johnson

Dean@ERYC said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top