Trouble with Autofill!!

[Peter Bernadyne]

Hello,

I have a range on a worksheet which at any given time may vary as t
length, etc. As such, I am attempting to use variables in the autofil
function in my macro to achieve a correct autofill. I am using th
following:

Selection.AutoFill Destination:=Range(ActiveCell, Cells(universe, 6))

Where 'universe' is a variable which counts the length of the univers
of data I have for which to perform the autofill (sometimes 20 rows
some times 15,000, etc.) and I am using 6 as the column index becaus
the fill on the worksheet takes place in column 'F' as the extrem
column of my fill range.

Whenever I try this, however, I get the following error:

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

Any hints would be greatly appreciated.

Thanks in advance.

-Pet

 

[Bernie Deitrick]

Peter,

Using Selection and Activecell with autofill can lead to problems. Since
you know what you want to copy, be specific - to fill down the formula from
cell F1, use:

Range("F1").AutoFill Destination:=Range("F2", Cells(Universe, 6))

HTH,
Bernie
MS Excel MVP

 

[Peter Bernadyne]

Hi Bernie,

Thanks very much for your reply.

Unfortunately, I've tried this to no avail. I keep getting the sam
error message.

I'm not sure what causes autofill to work sometimes and not others
it's a bit strange.

Any other ideas, perhaps?

-Pete

 

[Bernie Deitrick]

Peter,

It could be that you value of Universe is bad: check what it is when you get
the error. IIRC, Excel doesn't like when it is less than the row of the
cell being autofilled...

HTH,
Bernie
MS Excel MVP

 

[Peter Bernadyne]

Hi,

Sorry for the delayed reply - and I wish I could make this go away, bu
I'm still stuck with this annoying autofill problem.

My universe value is fine, when I check it in Debug mode, and I kno
that '6' is correct for the column ('F').

Could it be that I'm not referring to or omitting to declare my star
cells correctly? To make it very simple, I have a universe in column
'C' & 'D' which will always vary (hence I count the 'universe'). I'
trying to fill in two formulas contained in the top row of columns 'E
& 'F', so all I'm doing is selecting:

Range("E1:F1").Select

and

Selection.AutoFill Destination:=Range(ActiveCell, Cells(universe, 6))

which I figure should work out. What little changes I try either giv
me the Range method or the Autofill method failing.

Sorry to be a pester, but if anyone has any ideas, they would b
greatly appreciated.

Thanks,

-Pete

 

[Bernie Deitrick]

Peter,

The multiple cells makes a difference. Try this, which worked for me:

Range("E1:F1").AutoFill Destination:=Range("E1:F" & universe)

HTH,
Bernie
MS Excel MVP

 

[Peter Bernadyne]

Bernie,

It worked!!!

That's awesome! Thanks very much for your help!

-Pete