CODEWITHSUNDEEP
vbaexcel
Markus Knopfler
Markus Knopfler

Reputation: 637

Autofill in Excel VBA returns error 1004

I am trying to get a raw Excel file into a customized format. I added a picture below, so its easier to explain. I will address the requirements as steps too.

1) I need to get rid off all columns which include "Importo" or "Prezzo" 2) I need to extract the date from the remaining columns (Quantitá). First, I insert an empty row on top and then i apply right(cell,7).

So far, so good. Then I want to autofill the remaining columns, but i get a 1004 error. In the example code I tried from J:O, but really id need it from J to the last column. I post the code (which works until the last row).

I was actually wondering if Autofilling is best practise here, maybe indexing though would be better?

Sub delete_col()
 Dim A As Range

    Do
        Set A = Rows(1).Find(What:="Importo", LookIn:=xlValues, lookat:=xlPart)
        If A Is Nothing Then Exit Do
        A.EntireColumn.Delete
    Loop

    Do
        Set A = Rows(1).Find(What:="Prezzo", LookIn:=xlValues, lookat:=xlPart)
        If A Is Nothing Then Exit Do
        A.EntireColumn.Delete
    Loop

Rows("1:1").Select
ActiveCell.EntireRow.Insert

ActiveCell.Range("J1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(R\[1\]C,7)"

Selection.AutoFill Destination:=ActiveCell.Range("J1:O1"), Type:=xlFillDefault

End Sub

enter image description here

Upvotes: 1

Views: 186

Answers (1)

Vityata
Vityata

Reputation: 43585

I suppose that in the line ActiveCell.FormulaR1C1 = "=RIGHT(R\[1\]C,7)", the \ is a kind of typo, which should be deleted.

Concerning the 1004 error, the easiest way to go around it, while doing AutoFill, is something like this:

Sub TestMe()
    Range("A1:O1") = Range("J1")
End Sub

Thus, every value in Range("A1:O1") will be set with the value from Range("J1").

However, your code uses a lot of Select, Activate and ActiveCell. Try to avoid these, because they are not considered good practices in VBA and may lead to different errors. How to avoid using Select in Excel VBA

Upvotes: 2

Related Questions