Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop and autofill | Excel Discussion (Misc queries) | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming |