MS Excel 2013 Macro Runtime Error 1004 on Formula FillDown

brownrhon

New Member
Joined
Feb 2, 2016
Messages
2
I am failing to find how to make this new macro run smoothly in other worksheets. The code fails when I copy down the clean formula to the bottom of the column (underlined and in Italic below). I have gone through the forum and sorry, but I am not seeing a solution to work. Thanks for any clues you can send my way.



Sub Clean_Step1_10Col()
'
' Clean_Step1_10Col Macro
' Filter out Blanks ColA Create Col Insert Formula Copy Down
'


'
Cells.Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveSheet.Range("$A$1:$AD$7169").AutoFilter Field:=1, Criteria1:="<>"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
ActiveWindow.SmallScroll ToRight:=2
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Columns("S:S").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("D1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("F1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("H1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("J1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("L1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("N1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Range("P1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("R1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
Range("T1").Select
ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
Selection.FillDown
End Sub
Sub Clean_Step2_10Col()
'
' Clean_Step2_10Col Macro
' Copy/Paste/Delete CleanFormula Field Remove Filter
'


'
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Copy
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Copy
Columns("F:F").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Copy
Columns("G:G").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Selection.Copy
Columns("I:I").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
Selection.Copy
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.AutoFilter
End Sub
 

brownrhon

New Member
Joined
Feb 2, 2016
Messages
2
Solved
Jie Jenn's video helped me, "Excel 2007 VBA - Create a Macro that Auto Fill Cells Method 2"
[h=1][/h]Dim LR As Long
LR = Range("A666666").End(xlUp).Row

Range("B1").AutoFill Destination:=Range("B1:B" & LR)

I am sure there is a dynamic way of doing this, but happy to resolved it for my "training wheels" level.
 

Forum statistics

Threads
1,082,283
Messages
5,364,268
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top