#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 117
Default Auto Autofill

I want to autofill a formula down 17 rows, however my couln will be different
each time. Is their a way I can specify the distance to autofill with out
putting in the column exactly.

Heres my code so far:
Do
If IsEmpty(DestCell.Value) Then
Exit Do
Else
Set DestCell = DestCell.Offset(0, 1)
End If
Loop

DestCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
ActiveCell.Select
Selection.AutoFill Destination:=

any suggestions?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Auto Autofill

Can you use a certain row to determine the next available column? I like to
start at the far right and come back to the last used column, then come to the
right one column:

Dim Destcell as range
with worksheets("sheet9999")
set destcell = .cells(1,.columns.count).end(xltoleft).offset(1,0)
end with
destcell.resize(17,1).formular1c1 = "=RC[-2]-RC[-1]"

If you can't use the .end(xltoleft), then the destcell.resize(17,1) may be all
you need???

Jase wrote:

I want to autofill a formula down 17 rows, however my couln will be different
each time. Is their a way I can specify the distance to autofill with out
putting in the column exactly.

Heres my code so far:
Do
If IsEmpty(DestCell.Value) Then
Exit Do
Else
Set DestCell = DestCell.Offset(0, 1)
End If
Loop

DestCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
ActiveCell.Select
Selection.AutoFill Destination:=

any suggestions?


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,344
Default Auto Autofill

Hi,

Maybe something like this

Selection.AutoFill Destination:=Range(Selection, Selection.Offset(16, 0))

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Jase" wrote:

I want to autofill a formula down 17 rows, however my couln will be different
each time. Is their a way I can specify the distance to autofill with out
putting in the column exactly.

Heres my code so far:
Do
If IsEmpty(DestCell.Value) Then
Exit Do
Else
Set DestCell = DestCell.Offset(0, 1)
End If
Loop

DestCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
ActiveCell.Select
Selection.AutoFill Destination:=

any suggestions?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
autofill or auto reference stephen Excel Discussion (Misc queries) 2 July 17th 08 09:37 PM
Auto-populate, Auto-copy or Auto-fill? Jay S. Excel Worksheet Functions 4 August 10th 07 10:04 PM
How to AUTO SAVE as opposed to turning on auto recovery: EXCEL T-mo Excel Discussion (Misc queries) 1 January 12th 06 11:16 PM
Autofill: Need to autofill one week block, (5) weekday only into cells. dstock Excel Discussion (Misc queries) 1 June 17th 05 09:21 PM
excel links update not working in auto, calculations in auto Mikey Boy Excel Worksheet Functions 0 December 8th 04 12:53 AM


All times are GMT +1. The time now is 05:17 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"