autofill range

  • Thread starter Thread starter Stephen
  • Start date Start date
S

Stephen

I have a formula in cell F4 that i want to copy down to
the end of the data range. the range fluxuates daily. some
days 30 rows..some days 80 rows. so no matter what the row
range the macro automatically fills the formula down to
the bottom of the range column. Here is what I have so
far. But not working.

Range("F4").Select
ActiveCell.FormulaR1C1 = "=NETWORKDAYS(R1C5,RC[-1])"
Range("F4").Select
Range(ActiveCell, ActiveCell.End(xlDown)).AutoFill
 
How do you know that last row?

I used column A to determine it:

With activesheet
.range("f4:f" & .cells(.rows.count,"A").end(xlup).row).formular1c1 _
= "=NETWORKDAYS(R1C5,RC[-1])"
End with
I have a formula in cell F4 that i want to copy down to
the end of the data range. the range fluxuates daily. some
days 30 rows..some days 80 rows. so no matter what the row
range the macro automatically fills the formula down to
the bottom of the range column. Here is what I have so
far. But not working.

Range("F4").Select
ActiveCell.FormulaR1C1 = "=NETWORKDAYS(R1C5,RC[-1])"
Range("F4").Select
Range(ActiveCell, ActiveCell.End(xlDown)).AutoFill
Click to expand...
 
Stephen

try this:

Range("F4").FormulaR1C1 = "=NETWORKDAYS(R1C5,RC[-1])"
Range("F4").AutoFill _
Range(Range("F4"), Range("F4").Offset(0, -1).End(xlDown).Offset(0,
1))

Regards

Trevor
 
I assume here that your "data range" is 1 column to the left.

Sub Demo()
With [F4].Offset(0, -1)
With Range(.Address, .End(xlDown)).Offset(0, 1)
.Cells(1).FormulaR1C1 = "=NETWORKDAYS(R1C5,RC[-1])"
.FillDown
End With
End With
End Sub
 
I changed the layout of my page a little and your code
didn't work. but it worked the first time.

i have the first dates in column E starting at E3, I would
like the have the networkdays formula in Column F starting
in F3, and I have the Absolute Value date in $K$2.

Tks.

-----Original Message-----
I assume here that your "data range" is 1 column to the left.

Sub Demo()
With [F4].Offset(0, -1)
With Range(.Address, .End(xlDown)).Offset(0, 1)
.Cells(1).FormulaR1C1 = "=NETWORKDAYS(R1C5,RC[- 1])"
.FillDown
End With
End With
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


I have a formula in cell F4 that i want to copy down to
the end of the data range. the range fluxuates daily. some
days 30 rows..some days 80 rows. so no matter what the row
range the macro automatically fills the formula down to
the bottom of the range column. Here is what I have so
far. But not working.

Range("F4").Select
ActiveCell.FormulaR1C1 = "=NETWORKDAYS(R1C5,RC[-1])"
Range("F4").Select
Range(ActiveCell, ActiveCell.End(xlDown)).AutoFill
Click to expand...


.
Click to expand...
 
I keep getting an error at the .cell(.row.count
part of your code.

I have the first dates in column e, starting at e3, i have
the empty cells in column f to fill with the networkdays
formula, i have the absolute value data in $k$2.

tks.

-----Original Message-----
How do you know that last row?

I used column A to determine it:

With activesheet
.range("f4:f" & .cells(.rows.count,"A").end (xlup).row).formular1c1 _
= "=NETWORKDAYS(R1C5,RC[-1])"
End with
I have a formula in cell F4 that i want to copy down to
the end of the data range. the range fluxuates daily. some
days 30 rows..some days 80 rows. so no matter what the row
range the macro automatically fills the formula down to
the bottom of the range column. Here is what I have so
far. But not working.

Range("F4").Select
ActiveCell.FormulaR1C1 = "=NETWORKDAYS(R1C5,RC[-1])"
Range("F4").Select
Range(ActiveCell, ActiveCell.End(xlDown)).AutoFill
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
You dropped a few s's from the original code:

..cells not .cell
..rows.count not .row.count


With activesheet
.range("f4:f" & .cells(.rows.count,"A").end(xlup).row).formular1c1 _
= "=NETWORKDAYS(R1C5,RC[-1])"
End with

And change that "A" to the column that defines the lastrow (E?).


I keep getting an error at the .cell(.row.count
part of your code.

I have the first dates in column e, starting at e3, i have
the empty cells in column f to fill with the networkdays
formula, i have the absolute value data in $k$2.

tks.
-----Original Message-----
How do you know that last row?

I used column A to determine it:

With activesheet
.range("f4:f" & .cells(.rows.count,"A").end (xlup).row).formular1c1 _
= "=NETWORKDAYS(R1C5,RC[-1])"
End with
I have a formula in cell F4 that i want to copy down to
the end of the data range. the range fluxuates daily. some
days 30 rows..some days 80 rows. so no matter what the row
range the macro automatically fills the formula down to
the bottom of the range column. Here is what I have so
far. But not working.

Range("F4").Select
ActiveCell.FormulaR1C1 = "=NETWORKDAYS(R1C5,RC[-1])"
Range("F4").Select
Range(ActiveCell, ActiveCell.End(xlDown)).AutoFill
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
Click to expand...
 
Back
Top