Excel autofill w/variable range? 1

[kidvegas19]

Why does this work:

Range(ActiveCell, ActiveCell.Offset(4, 4)).Select

But this doesn't:

Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(4, 4)), Type:=xlFillDefault

Autofill method of range class failed
????

Objective: When I run this macro, I want to autofill from the static range of AI2:AI6 to a variable range ??2:??6
I thought I could do this by using a variable for the column offset, but obviously I can't.

As always, help is greatly appreciated.

kv

 

[rmikesmith]

kv,

Two observations:

The destination range must include the source range [from the Help file]

It appears that you cannot autofill in two dimensions in a single operation. If you try to do this manually (through the Excel interface), you'll notice that you can drag the fill indicator down or across, but not both. Likewise, attempting this in code generates the error you indicated.


HTH
Mike

 

[kidvegas19]

Thanks for the info Mike.

Some points of clarification. I thought that

Range(ActiveCell, ActiveCell.Offset(4, 0)).Select
Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell(4, 4)), Type:=xlFillDefault

would do the trick. But hey, what do I know? LOL!

Second, I am not going in 2 dimensions. I can see how my original post could indicate that. the ?? in the variable range represent the same column.

My problem is that I know 3 of the 4 elements of the autofill process. I know the source columns, the source rows, and the destination rows (same as the source rows). The destination column is the variable. I can't figure out how to translate my variable into a column coordinate in the destination range. I will always start at column AI. If my variable is one, I need to autofill to column AJ. If it's 5 I need to autofill to column AN, etc etc.

Off for more research (more like guess-search).

Thanks again,

kv

 

[kidvegas19]

I got this to work for me.

Range("AI2").Select
Dim rangevar As Integer
rangvar = 4
Set myrange = Range(ActiveCell, ActiveCell.Offset(4, rangvar))
Set startrange = Range(ActiveCell, ActiveCell.Offset(4, 0))

Set SourceRange = startrange
Set fillRange = myrange
SourceRange.AutoFill Destination:=fillRange

Voila! Now I can spend the rest of my Saturday watching the flood-inducing rainstorm.

Thanks again and I hope this will help somebody else as well.

kv

 

[rmikesmith]

kv,

Here is a cleaned-up version of your procedure that reduces the number of variables used but more importantly doesn't 'select' anything (which is rarely necessary). Instead, it references specific ranges directly. You can also see how to easily change the starting column you want it to operate from (e.g., Cells(2,35)= Range("AI2")):

Sub AutoFillProc()
Dim SourceRng As Range
Dim FillRng As Range
Dim ColumnOffset As Integer


   ColumnOffset = 4
   
   Set SourceRng = Range(Cells(2, 35), Cells(2, 35).Offset(4, 0))
   Set FillRng = Range(Cells(2, 35), Cells(2, 35).Offset(4, ColumnOffset))
   
   SourceRng.AutoFill Destination:=FillRng
   
End Sub

You could also simplify things further by eliminating the Offset property and just adjusting the Cells coordinates. Example:

Set SourceRng = Range(Cells(2, 35), Cells(6, 35))

and

Set FillRng = Range(Cells(2, 35), Cells(6,ColumnOffset+35))

Regards,
Mike

 

[kidvegas19]

Wow. Good stuff Mike, thanks. Besides this particular aspect of the overall project, I can use your latest information in other areas as well. Thanks for the good work. I researched the cells property but didn't put two and two together until you clarified it here.

Thanks again,

kv