Autofill macro question

[Kell2604]

Hi Guys,

I need some assistance with a macro. I need the macro to grab some formulas
from a static range of cells (B2 - G2) and paste that formula in a static
range (B4 - G4) and then I need it to autofill to the last empty row (the end
of this range is always changing or else I would use a static end range).
For example, today I might need it to fill all the way to B36 - G36 and
tomorrow I might need it to fill to B77 - G77. The end of the range is
determined by the column immediately preceeding the formulas. In my example
above A36 or A77.

A B C D E F G H
1 x formulas............
2 x fill formulas........
3 x fill formulas........
4
5
6 x end formula fill....
7
8
9

My current Macro:
lRow = Cells(Rows.Count, "AG").End(xlUp).Row
Range("AG4").AutoFill Destination:=Range("AG4:AM" & lRow)

Gives the following error:
AutoFill method of Range class failed.

Thanks as always for your help!!
Kelley

 

[Per Jessen]

Hi

The destination range has to include the source range ( same columns).

lRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:M2").AutoFill Destination:=Range("B2:G" & lRow)

Hopes it helps

 

[Joergen Bondesen]

Hi Kelly

I'm not sure about your question, Try below.


Option Explicit


'----------------------------------------------------------
' Procedure : FillDown
' Date : 20090120
' Author : Joergen Bondesen
' Modifyed by :
' Purpose :
' Note :
'----------------------------------------------------------
'
Sub FillDown()
Dim lRow As Long
lRow = Cells(Rows.Count, "A").End(xlUp).Row

Dim SourceRange As Range
Set SourceRange = Range("B2:G2")

Dim fillRange As Range
Set fillRange = Range("B2:g" & lRow)

SourceRange.AutoFill Destination:=fillRange

Set SourceRange = Nothing
Set fillRange = Nothing
End Sub

 

[Ronald R. Dodge, Jr.]

I have a similar situation with us as a company using ShowCase Query by
SPSS. In that case, the first row of the extendable data area is the
constant row while the last row is the variable row. Not only that, given
the product requires a minimal of 2 rows to be the data area so as rows can
be inserted within the range without causing insertion issues, it does
create a special case issue for when there is only 1 record or when there is
no records. I won't get into that part of it as that's not what you asking
for, but you can do one of 2 things:



Method 1: Using Formula Row as the Formula basis.

Name the entire range where your formula will be such as "rngExtendableArea"

Name the row with your formulas as "rngFormulaRow"

Use the following line:

ThisWorkbook.Names("rngFormulaRange").RefersToRange.Copy _
Thisworkbook.Names("rngExtendableArea").RefersToRange

Be sure the number of columns is the same number for both range names.



Method 2: Use the Fill Down Method

Name the entire range where your formula will be such as "rngExtendableArea"

You could then use a such command like:

ThisWorkbook.Names("rngExtendableArea").RefersToRange.FillDown

However, be forewarned, when using this method, if the range is only 1 row,
it will copy from the row above this one row (if it's not the first row of
the worksheet).


As a side note, if you plan on using these ranges multiple times within
code, you can set these ranges to a Range object variable.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Kell2604]

This worked great Per - your AWESOME!! Thank you so much!

 

[Per Jessen]

Thanks for your reply, I'm glad to help.

Regards,
Per


- Snip -