Problem with incrementing autofill in VBA

[Joe4]

I am trying to autofill two columns of data with numeric values that I do NOT want to increment. I have tried formatting the columns as Text and entering them in as text as well, but it makes no difference.

Here is the code:

Sub MyFormat()

'   Capture last row
    Dim myLastRow As Long
    myLastRow = Range("A65536").End(xlUp).Row

'   Autofill columns E & F
    Columns("E:F").NumberFormat = "@"
    Range("E1").FormulaR1C1 = "'0001"
    Range("E1").AutoFill Destination:=Range("E1:E" & myLastRow)
    Range("F1").FormulaR1C1 = "'1"
    Range("F1").AutoFill Destination:=Range("F1:F" & myLastRow)

End Sub

The desired output is:
0001 1
0001 1
0001 1
etc.

I am getting:
0001 1
0002 2
0003 3
etc.

How do I keep autofill from incrementing in my VBA code?

Thanks.

 

[Von Pookie]

You want FillDown, not Autofill

Sub MyFormat()

'   Capture last row
    Dim myLastRow As Long
    myLastRow = Range("A65536").End(xlUp).Row

'   Autofill columns E & F
    Columns("E:F").NumberFormat = "@"
    Range("E1").FormulaR1C1 = "'0001"
    Range("E1:E" & myLastRow).FillDown
    Range("F1").FormulaR1C1 = "'1"
    Range("F1:F" & myLastRow).FillDown

End Sub

 

[Joe4]

Well, its a ugly workaround, but it works. If anyone has anything better, I'd love to see it!

Sub MyFormat()

'   Capture last row
    Dim myLastRow As Long
    myLastRow = Range("A65536").End(xlUp).Row

'   Autofill columns E & F
    Columns("E:F").NumberFormat = "@"
    Range("E1").FormulaR1C1 = "'0001"
    Range("E2").FormulaR1C1 = "'0001"
    Range("E1:E2").AutoFill Destination:=Range("E1:E" & myLastRow)
    Range("F1").FormulaR1C1 = "'1"
    Range("F2").FormulaR1C1 = "'1"
    Range("F1:F2").AutoFill Destination:=Range("F1:F" & myLastRow)

End Sub

 

[Joe4]

YES! That does it. Thanks Kristy. I was not familiar with FillDown.

Thank you.

 

[Bookmaster]

Hello,

I have a similar problem. Here is my VBA:

Range("C2").Select
    ActiveCell.FormulaR1C1 = "NAZIV WEBSHOP"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "NAZIV ZA PRETRAGU WEBSHOP"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "SIFRA ARTIKLA WEBSHOP"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "OSOBINA1"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "OSOBINA2"
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "OSOBINA3"
    Range("C8").Select
    ActiveCell.FormulaR1C1 = "OSOBINA4"
    Range("C9").Select
    ActiveCell.FormulaR1C1 = "OSOBINA5"
    Range("C10").Select
    ActiveCell.FormulaR1C1 = "OSOBINA6"
    Range("C11").Select
    ActiveCell.FormulaR1C1 = "OSOBINA7"
    Range("C12").Select
    ActiveCell.FormulaR1C1 = "SLIKA ARTIKLA"
    Range("C13").Select
    ActiveCell.FormulaR1C1 = "OBJAVA U KATALOGU"
    Range("C14").Select
    ActiveCell.FormulaR1C1 = "KATEGORIJA WEBSHOP"
   
   
     Dim LR As Long
    LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("C2:C14").AutoFill Destination:=Range("C2:C" & myLastRow)

When I start it I get values "Osobina" to increase instead of to repeat. Here is what I'm getting:

NAZIV WEBSHOP

NAZIV ZA PRETRAGU WEBSHOP

SIFRA ARTIKLA WEBSHOP

OSOBINA1

OSOBINA2

OSOBINA3

OSOBINA4

OSOBINA5

OSOBINA6

OSOBINA7

SLIKA ARTIKLA

OBJAVA U KATALOGU

KATEGORIJA WEBSHOP

NAZIV WEBSHOP

NAZIV ZA PRETRAGU WEBSHOP

SIFRA ARTIKLA WEBSHOP

OSOBINA8

OSOBINA9

OSOBINA10

OSOBINA11

OSOBINA12

OSOBINA13

OSOBINA14

SLIKA ARTIKLA

OBJAVA U KATALOGU

KATEGORIJA WEBSHOP

I don't want numbers with OSOBINA to increase after number 7. I want them to be exactly as I sort them from 1 up to 7 and to repeat.

Where am I wrong?

 

[RoryA]

Autofill also has a second Type argument, which you can specify to be xlFillCopy

 

[Bookmaster]

Great, thank you.
At the end I should put:

Dim LR As Long
    LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Range("C2:C14").AutoFill Destination:=Range("C2:C" & LR), Type:=xlFillCopy

Thank you once again.

 

[RoryA]

Try it and see...