Trouble with Autofill!!

  • Thread starter Thread starter Peter Bernadyne
  • Start date Start date
P

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
 
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
 
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
 
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
 
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
 
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
 
Back
Top