Autofill Error

Hi,

I need a macro to autofill COLUMN A with a word say YES until the last row 
found in COLUMN B.

Right now I have the ff codes:

Sheets("VOUCHER - STEP 2").Select
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "DEBIT"
    With ActiveCell.Characters(Start:=1, Length:=5).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Set Voucher2 = Worksheets("VOUCHER - STEP 2")
    Range("A5").Select
    Selection.AutoFill Destination:=Range("A5:" & LastRow(Voucher2))

It shows an error on the autofill range. THANKS!
0
Utf
2/13/2010 11:50:01 AM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
741 Views

Similar Articles

[PageSpeed] 10

Hi,

Maybe this

Sub Fill_Yes()
Set sht = Sheets("VOUCHER - STEP 2")
LastRow = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
    sht.Range("A5").Value = "DEBIT"
    With sht.Range("A5").Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .ColorIndex = xlAutomatic
    End With
   sht.Range("A6:A" & LastRow).Value = "Yes"
End Sub

-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"ch-d" wrote:

> Hi,
> 
> I need a macro to autofill COLUMN A with a word say YES until the last row 
> found in COLUMN B.
> 
> Right now I have the ff codes:
> 
> Sheets("VOUCHER - STEP 2").Select
>     Range("A5").Select
>     ActiveCell.FormulaR1C1 = "DEBIT"
>     With ActiveCell.Characters(Start:=1, Length:=5).Font
>         .Name = "Arial"
>         .FontStyle = "Regular"
>         .Size = 8
>         .Strikethrough = False
>         .Superscript = False
>         .Subscript = False
>         .OutlineFont = False
>         .Shadow = False
>         .Underline = xlUnderlineStyleNone
>         .ColorIndex = xlAutomatic
>     End With
>     Set Voucher2 = Worksheets("VOUCHER - STEP 2")
>     Range("A5").Select
>     Selection.AutoFill Destination:=Range("A5:" & LastRow(Voucher2))
> 
> It shows an error on the autofill range. THANKS!
0
Utf
2/13/2010 12:11:01 PM
You have a lot of unneeded code and selections are rarely needed. The main 
problem seems to be with the filldown range. Try something like this code:

Sub FillA5Down()
Dim sht As Worksheet
Set sht = Sheets("VOUCHER - STEP 2")
    sht.Range("A5").Value = "DEBIT"
    With sht.Range("A5").Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .ColorIndex = xlAutomatic
    End With
   sht.Range("A5", Range("B5").End(xlDown)).FillDown
End Sub

Mike F
"ch-d" <chd@discussions.microsoft.com> wrote in message 
news:07CB84AD-7749-4F51-9493-ACA8B5D3A1D4@microsoft.com...
> Hi,
>
> I need a macro to autofill COLUMN A with a word say YES until the last row
> found in COLUMN B.
>
> Right now I have the ff codes:
>
> Sheets("VOUCHER - STEP 2").Select
>    Range("A5").Select
>    ActiveCell.FormulaR1C1 = "DEBIT"
>    With ActiveCell.Characters(Start:=1, Length:=5).Font
>        .Name = "Arial"
>        .FontStyle = "Regular"
>        .Size = 8
>        .Strikethrough = False
>        .Superscript = False
>        .Subscript = False
>        .OutlineFont = False
>        .Shadow = False
>        .Underline = xlUnderlineStyleNone
>        .ColorIndex = xlAutomatic
>    End With
>    Set Voucher2 = Worksheets("VOUCHER - STEP 2")
>    Range("A5").Select
>    Selection.AutoFill Destination:=Range("A5:" & LastRow(Voucher2))
>
> It shows an error on the autofill range. THANKS! 


0
Mike
2/13/2010 1:57:18 PM
Sub FillA5Down()
Dim sht As Worksheet
Set sht = Sheets("sheet8")
    sht.Range("A5").Value = "DEBIT"
    With sht.Range("A5").Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 8
        .ColorIndex = xlAutomatic
    End With
    lr = sht.Range("B5").End(xlDown).Row
    sht.Range(Cells(5, 1), Cells(lr, 1)).FillDown
  End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Mike Fogleman" <mikefogleman@comcast.net> wrote in message 
news:uSBR1RLrKHA.3536@TK2MSFTNGP06.phx.gbl...
> You have a lot of unneeded code and selections are rarely needed. The main 
> problem seems to be with the filldown range. Try something like this code:
>
> Sub FillA5Down()
> Dim sht As Worksheet
> Set sht = Sheets("VOUCHER - STEP 2")
>    sht.Range("A5").Value = "DEBIT"
>    With sht.Range("A5").Font
>        .Name = "Arial"
>        .FontStyle = "Regular"
>        .Size = 8
>        .ColorIndex = xlAutomatic
>    End With
>   sht.Range("A5", Range("B5").End(xlDown)).FillDown
> End Sub
>
> Mike F
> "ch-d" <chd@discussions.microsoft.com> wrote in message 
> news:07CB84AD-7749-4F51-9493-ACA8B5D3A1D4@microsoft.com...
>> Hi,
>>
>> I need a macro to autofill COLUMN A with a word say YES until the last 
>> row
>> found in COLUMN B.
>>
>> Right now I have the ff codes:
>>
>> Sheets("VOUCHER - STEP 2").Select
>>    Range("A5").Select
>>    ActiveCell.FormulaR1C1 = "DEBIT"
>>    With ActiveCell.Characters(Start:=1, Length:=5).Font
>>        .Name = "Arial"
>>        .FontStyle = "Regular"
>>        .Size = 8
>>        .Strikethrough = False
>>        .Superscript = False
>>        .Subscript = False
>>        .OutlineFont = False
>>        .Shadow = False
>>        .Underline = xlUnderlineStyleNone
>>        .ColorIndex = xlAutomatic
>>    End With
>>    Set Voucher2 = Worksheets("VOUCHER - STEP 2")
>>    Range("A5").Select
>>    Selection.AutoFill Destination:=Range("A5:" & LastRow(Voucher2))
>>
>> It shows an error on the autofill range. THANKS!
>
> 

0
Don
2/13/2010 2:24:10 PM
To Mike H, Mike Fogleman and Don Guillett: THANKS A BUNCH! you guys helped me 
alot as always! ;-) THANKS!
0
Utf
2/14/2010 4:32:01 PM
Reply:

Similar Artilces: