AutoFill Loop
 

HI all, I am still pretty new at this VBA stuff. I have a need to select a
formula in a spreadsheet and fill it down to the last cell possible, then
offset over the the right 9 cell and Autofill again, offsett 9 cells and
repeat until it can't find a activecell with info in it.

The problem is the cells I am Autofilling are all different amounts of fill
some are 600 rows and some are 6000.

Please help if possible.
--
Pete

AutoFill Loop
 

Using example locations:
set rng = Range(cells(2,"B"),cells(rows.count,"B").End(xlup) )

gives you a reference to the filled cells. then if you wanted to fill
column C

rng.offset(0,1).Formula = rng.offset(0,1).Resize(1,1).Formula

or for autofill possibly

rng.offset(0,1).Resize(1,1).Autofill Destination:=rng.offset(0,1)

--
Regards,
Tom Ogilvy



"Pete" wrote in message
...
HI all, I am still pretty new at this VBA stuff. I have a need to select a
formula in a spreadsheet and fill it down to the last cell possible, then
offset over the the right 9 cell and Autofill again, offsett 9 cells and
repeat until it can't find a activecell with info in it.

The problem is the cells I am Autofilling are all different amounts of
fill
some are 600 rows and some are 6000.

Please help if possible.
--
Pete

AutoFill Loop
 

Tom

Thanks for your help, but I am not following your code.

Here is what I got so far
Sub PB_Get_Nums()
ActiveCell.Offset(1, 5).Range("A1").Select
Range("F2").End(xlDown).Offset(0, 1).FillDown
Do
With ActiveCell
ActiveCell.Offset(0, 9).Range("A1").Select


`Need autofill code here to fill down to last cell.


End With
Loop Until IsEmpty(ActiveCell.Value)
End Sub

I could simply tell it to select the next cell in my sheet ("O2") but I have
roughly 45 of these to do in 8 different workbooks and want to loop through
it to save time. I know it's probably an easy fix, but I am not seeing it.
--
Pete


"Tom Ogilvy" wrote:

Using example locations:
set rng = Range(cells(2,"B"),cells(rows.count,"B").End(xlup) )

gives you a reference to the filled cells. then if you wanted to fill
column C

rng.offset(0,1).Formula = rng.offset(0,1).Resize(1,1).Formula

or for autofill possibly

rng.offset(0,1).Resize(1,1).Autofill Destination:=rng.offset(0,1)

--
Regards,
Tom Ogilvy



"Pete" wrote in message
...
HI all, I am still pretty new at this VBA stuff. I have a need to select a
formula in a spreadsheet and fill it down to the last cell possible, then
offset over the the right 9 cell and Autofill again, offsett 9 cells and
repeat until it can't find a activecell with info in it.

The problem is the cells I am Autofilling are all different amounts of
fill
some are 600 rows and some are 6000.

Please help if possible.
--
Pete

AutoFill Loop
 

Sub PB_Get_Nums()
Range("F2").Select
Do
With ActiveCell
.End(xlDown).Offset(0, 1).FillDown

.Offset(0, 9).Select

End With
Loop Until IsEmpty(ActiveCell.Value)
End Sub

--
Regards,
Tom Ogilvy


"Pete" wrote in message
...
Tom

Thanks for your help, but I am not following your code.

Here is what I got so far
Sub PB_Get_Nums()
ActiveCell.Offset(1, 5).Range("A1").Select
Range("F2").End(xlDown).Offset(0, 1).FillDown
Do
With ActiveCell
ActiveCell.Offset(0, 9).Range("A1").Select


`Need autofill code here to fill down to last cell.


End With
Loop Until IsEmpty(ActiveCell.Value)
End Sub

I could simply tell it to select the next cell in my sheet ("O2") but I
have
roughly 45 of these to do in 8 different workbooks and want to loop
through
it to save time. I know it's probably an easy fix, but I am not seeing it.
--
Pete


"Tom Ogilvy" wrote:

Using example locations:
set rng = Range(cells(2,"B"),cells(rows.count,"B").End(xlup) )

gives you a reference to the filled cells. then if you wanted to fill
column C

rng.offset(0,1).Formula = rng.offset(0,1).Resize(1,1).Formula

or for autofill possibly

rng.offset(0,1).Resize(1,1).Autofill Destination:=rng.offset(0,1)

--
Regards,
Tom Ogilvy



"Pete" wrote in message
...
HI all, I am still pretty new at this VBA stuff. I have a need to
select a
formula in a spreadsheet and fill it down to the last cell possible,
then
offset over the the right 9 cell and Autofill again, offsett 9 cells
and
repeat until it can't find a activecell with info in it.

The problem is the cells I am Autofilling are all different amounts of
fill
some are 600 rows and some are 6000.

Please help if possible.
--
Pete

AutoFill Loop
 

Tom, I'm sorry to such a pest, but your code does not work properly. I can
follow you logic on the loop and the code does loop properly, however it does
not fill down the formula on the selected cell during the loop.
--
Pete


"Tom Ogilvy" wrote:

Sub PB_Get_Nums()
Range("F2").Select
Do
With ActiveCell
.End(xlDown).Offset(0, 1).FillDown

.Offset(0, 9).Select

End With
Loop Until IsEmpty(ActiveCell.Value)
End Sub

--
Regards,
Tom Ogilvy


"Pete" wrote in message
...
Tom

Thanks for your help, but I am not following your code.

Here is what I got so far
Sub PB_Get_Nums()
ActiveCell.Offset(1, 5).Range("A1").Select
Range("F2").End(xlDown).Offset(0, 1).FillDown
Do
With ActiveCell
ActiveCell.Offset(0, 9).Range("A1").Select


`Need autofill code here to fill down to last cell.


End With
Loop Until IsEmpty(ActiveCell.Value)
End Sub

I could simply tell it to select the next cell in my sheet ("O2") but I
have
roughly 45 of these to do in 8 different workbooks and want to loop
through
it to save time. I know it's probably an easy fix, but I am not seeing it.
--
Pete


"Tom Ogilvy" wrote:

Using example locations:
set rng = Range(cells(2,"B"),cells(rows.count,"B").End(xlup) )

gives you a reference to the filled cells. then if you wanted to fill
column C

rng.offset(0,1).Formula = rng.offset(0,1).Resize(1,1).Formula

or for autofill possibly

rng.offset(0,1).Resize(1,1).Autofill Destination:=rng.offset(0,1)

--
Regards,
Tom Ogilvy



"Pete" wrote in message
...
HI all, I am still pretty new at this VBA stuff. I have a need to
select a
formula in a spreadsheet and fill it down to the last cell possible,
then
offset over the the right 9 cell and Autofill again, offsett 9 cells
and
repeat until it can't find a activecell with info in it.

The problem is the cells I am Autofilling are all different amounts of
fill
some are 600 rows and some are 6000.

Please help if possible.
--
Pete

AutoFill Loop
 

You, of course, hold the distinct advantage of being able to look at the
sheet, so based on your statement, I can only imagine that column F is the
definitive column for determining the extent of fill. If so


Sub PB_Get_Nums()
set rng = Range(Range("F2"),Range("F2).End(xldown))
for i = 0 to 27
if isempty(rng(1).offset(0,i*9)) then exit sub
rng.offset(0,i*9).Filldown
Next
End Sub

If not, change F2 to the correct cell.

--
Regards,
Tom Ogilvy



"Pete" wrote in message
...
Tom, I'm sorry to such a pest, but your code does not work properly. I can
follow you logic on the loop and the code does loop properly, however it
does
not fill down the formula on the selected cell during the loop.
--
Pete


"Tom Ogilvy" wrote:

Sub PB_Get_Nums()
Range("F2").Select
Do
With ActiveCell
.End(xlDown).Offset(0, 1).FillDown

.Offset(0, 9).Select

End With
Loop Until IsEmpty(ActiveCell.Value)
End Sub

--
Regards,
Tom Ogilvy


"Pete" wrote in message
...
Tom

Thanks for your help, but I am not following your code.

Here is what I got so far
Sub PB_Get_Nums()
ActiveCell.Offset(1, 5).Range("A1").Select
Range("F2").End(xlDown).Offset(0, 1).FillDown
Do
With ActiveCell
ActiveCell.Offset(0, 9).Range("A1").Select


`Need autofill code here to fill down to last cell.


End With
Loop Until IsEmpty(ActiveCell.Value)
End Sub

I could simply tell it to select the next cell in my sheet ("O2") but
I
have
roughly 45 of these to do in 8 different workbooks and want to loop
through
it to save time. I know it's probably an easy fix, but I am not seeing
it.
--
Pete


"Tom Ogilvy" wrote:

Using example locations:
set rng = Range(cells(2,"B"),cells(rows.count,"B").End(xlup) )

gives you a reference to the filled cells. then if you wanted to
fill
column C

rng.offset(0,1).Formula = rng.offset(0,1).Resize(1,1).Formula

or for autofill possibly

rng.offset(0,1).Resize(1,1).Autofill Destination:=rng.offset(0,1)

--
Regards,
Tom Ogilvy



"Pete" wrote in message
...
HI all, I am still pretty new at this VBA stuff. I have a need to
select a
formula in a spreadsheet and fill it down to the last cell
possible,
then
offset over the the right 9 cell and Autofill again, offsett 9
cells
and
repeat until it can't find a activecell with info in it.

The problem is the cells I am Autofilling are all different
amounts of
fill
some are 600 rows and some are 6000.

Please help if possible.
--
Pete