Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill down a varying Range
Using Excel 2000
I am trying to use autofill to copy a formula down but the range will vary. M1 is the column header (Title) M2 has the following formula in: =IF(L2="","ERROR","") Column A always has a number in it for each row The number of rows will vary I have tried the following but it doesn't work - it fills M1 above it but not the rows below it! Dim howmany As Long howmany = Application.CountA("a:a") Set SourceRange = Range("M2:M2") 'Set fillRange = Range("M2:M19") Set fillRange = Range("M2:M" & howmany) SourceRange.AutoFill Destination:=fillRange Range("M2").Select Can someone let me know what I'm doing wrong. Cheers, Dean. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill down a varying Range
Dean@ERYC wrote: Using Excel 2000 I am trying to use autofill to copy a formula down but the range will vary. M1 is the column header (Title) M2 has the following formula in: =IF(L2="","ERROR","") Column A always has a number in it for each row The number of rows will vary I have tried the following but it doesn't work - it fills M1 above it but not the rows below it! Dim howmany As Long howmany = Application.CountA("a:a") Set SourceRange = Range("M2:M2") 'Set fillRange = Range("M2:M19") Set fillRange = Range("M2:M" & howmany) SourceRange.AutoFill Destination:=fillRange Range("M2").Select Can someone let me know what I'm doing wrong. Cheers, Dean. Hi Dean, try... Dim howmany As Long howmany = WorksheetFunction.CountA(Range("a:a")) Set SourceRange = Range("M2") Set fillRange = Range("M2:M" & howmany) SourceRange.AutoFill Destination:=fillRange Range("M2").Select Ken Johnson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill down a varying Range
Thanks Ken thats sorted it.
Thank-you very much. Dean "Ken Johnson" wrote: Dean@ERYC wrote: Using Excel 2000 I am trying to use autofill to copy a formula down but the range will vary. M1 is the column header (Title) M2 has the following formula in: =IF(L2="","ERROR","") Column A always has a number in it for each row The number of rows will vary I have tried the following but it doesn't work - it fills M1 above it but not the rows below it! Dim howmany As Long howmany = Application.CountA("a:a") Set SourceRange = Range("M2:M2") 'Set fillRange = Range("M2:M19") Set fillRange = Range("M2:M" & howmany) SourceRange.AutoFill Destination:=fillRange Range("M2").Select Can someone let me know what I'm doing wrong. Cheers, Dean. Hi Dean, try... Dim howmany As Long howmany = WorksheetFunction.CountA(Range("a:a")) Set SourceRange = Range("M2") Set fillRange = Range("M2:M" & howmany) SourceRange.AutoFill Destination:=fillRange Range("M2").Select Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill down a varying Range
Thanks Ken thats sorted it.
Thank-you very much. Cheers, Dean "Ken Johnson" wrote: Dean@ERYC wrote: Using Excel 2000 I am trying to use autofill to copy a formula down but the range will vary. M1 is the column header (Title) M2 has the following formula in: =IF(L2="","ERROR","") Column A always has a number in it for each row The number of rows will vary I have tried the following but it doesn't work - it fills M1 above it but not the rows below it! Dim howmany As Long howmany = Application.CountA("a:a") Set SourceRange = Range("M2:M2") 'Set fillRange = Range("M2:M19") Set fillRange = Range("M2:M" & howmany) SourceRange.AutoFill Destination:=fillRange Range("M2").Select Can someone let me know what I'm doing wrong. Cheers, Dean. Hi Dean, try... Dim howmany As Long howmany = WorksheetFunction.CountA(Range("a:a")) Set SourceRange = Range("M2") Set fillRange = Range("M2:M" & howmany) SourceRange.AutoFill Destination:=fillRange Range("M2").Select Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenate with a varying range of cells | Excel Worksheet Functions | |||
Subtotals in a range of varying size | Excel Programming | |||
Range varying in for loop | Excel Programming | |||
Using Varying Range Size in Macro | Excel Programming | |||
sumif range of varying size | Excel Worksheet Functions |