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

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

---
Per

"Kell2604" skrev i meddelelsen
...
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

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


--
Med venlig hilsen

Jørgen Bondesen


"Kell2604" skrev i en meddelelse
...
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

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").RefersToRang e.Copy _
Thisworkbook.Names("rngExtendableArea").RefersToRa nge

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").RefersToRa nge.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" wrote in message
...
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

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

"Per Jessen" wrote:

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

---
Per

"Kell2604" skrev i meddelelsen
...
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

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

Regards,
Per


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