autofill won't fill to end of used range

[NikkiS]

I am writing a macro to be used on a generated report. As part of the
macro, I need it to autofill formulas in various columns to the last row of
data, which can vary each time the report is generated. The first formula to
be filled is in col D. There are NO empty cells in column C (nor A & B for
that matter) in the used range (3000+ entries), but when the macro runs it
will only fill to row 605! However, when I insert a breakpoint just before
the formula autofills and manually double-click the hande to perform an
autofill, it works just fine. Needless to say, I don't want the user to have
to do this.

I can't find anything on the web addressing this issue. Does anyone have
any ideas?

Here is this portion of the macro:

Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).row
'Through out my macro, I need it to fill down based on col A, not the
preceding column

Columns("D").Select
Selection.ClearContents
Range("d1").Value = "Season"
Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"
Range("d2").Select
Selection.Copy
Selection.AutoFill Range("d2:d" & lastrow)


Thanks,
Nikki

 

[Don Guillett]

'Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"
Range("d2").FormulaR1C1 = "=[filename.xls]sheetname!rc[-1]"

I would have written it like this. NO SELECTIONS
Sub testautofill()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
With Range("d2") 'Only one dot . in front of next 4 lines
.EntireColumn.ClearContents
.Offset(-1) = "Season"
.Formula = "=[filename.xls]sheetname!rc[-1]"
.AutoFill Range("d2:d" & lastrow)
End With
End Sub

 

[madelca100]

Don,

What I would find most useful is if you pointed out where Nikki's code went
wrong.

There are probably dozens of ways to get the 'right' answer. Knowing what
line or lines of code prevented the code from producing the correct results
would be very valuable.

mike

'Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"

Range("d2").FormulaR1C1 = "=[filename.xls]sheetname!rc[-1]"

I would have written it like this. NO SELECTIONS
Sub testautofill()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
With Range("d2") 'Only one dot . in front of next 4 lines
.EntireColumn.ClearContents
.Offset(-1) = "Season"
.Formula = "=[filename.xls]sheetname!rc[-1]"
.AutoFill Range("d2:d" & lastrow)
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I am writing a macro to be used on a generated report. As part of the
macro, I need it to autofill formulas in various columns to the last row
of
data, which can vary each time the report is generated. The first formula
to
be filled is in col D. There are NO empty cells in column C (nor A & B
for
that matter) in the used range (3000+ entries), but when the macro runs it
will only fill to row 605! However, when I insert a breakpoint just
before
the formula autofills and manually double-click the hande to perform an
autofill, it works just fine. Needless to say, I don't want the user to
have
to do this.

I can't find anything on the web addressing this issue. Does anyone have
any ideas?

Here is this portion of the macro:

Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).row
'Through out my macro, I need it to fill down based on col A, not the
preceding column

Columns("D").Select
Selection.ClearContents
Range("d1").Value = "Season"
Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"
Range("d2").Select
Selection.Copy
Selection.AutoFill Range("d2:d" & lastrow)


Thanks,
Nikki

.

 

[Don Guillett]

I did...

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Don,

What I would find most useful is if you pointed out where Nikki's code
went
wrong.

There are probably dozens of ways to get the 'right' answer. Knowing what
line or lines of code prevented the code from producing the correct
results
would be very valuable.

mike

'Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"

Range("d2").FormulaR1C1 = "=[filename.xls]sheetname!rc[-1]"

I would have written it like this. NO SELECTIONS
Sub testautofill()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
With Range("d2") 'Only one dot . in front of next 4 lines
.EntireColumn.ClearContents
.Offset(-1) = "Season"
.Formula = "=[filename.xls]sheetname!rc[-1]"
.AutoFill Range("d2:d" & lastrow)
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I am writing a macro to be used on a generated report. As part of the
macro, I need it to autofill formulas in various columns to the last
row
of
data, which can vary each time the report is generated. The first
formula
to
be filled is in col D. There are NO empty cells in column C (nor A & B
for
that matter) in the used range (3000+ entries), but when the macro runs
it
will only fill to row 605! However, when I insert a breakpoint just
before
the formula autofills and manually double-click the hande to perform an
autofill, it works just fine. Needless to say, I don't want the user
to
have
to do this.

I can't find anything on the web addressing this issue. Does anyone
have
any ideas?

Here is this portion of the macro:

Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).row
'Through out my macro, I need it to fill down based on col A, not the
preceding column

Columns("D").Select
Selection.ClearContents
Range("d1").Value = "Season"
Range("d2").FormulaR1C1 = "=Personal.xlsb!season(rc[-1])"
Range("d2").Select
Selection.Copy
Selection.AutoFill Range("d2:d" & lastrow)


Thanks,
Nikki

.