Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time error 1004 in Autofill method of rangle class failed
Hello
A file has one line of header information and than only one line of data. When it hits the first autofill in the attached macro it gives a Run-Time error 1004 in Autofill method of range class failed. Can someone suggest a fix for this so it doesn't bring back this message or looks for it and bypasses the Autofill? It's aborting on this first Autofill. If I have the header record plus two records it won't have that message and everything is fine. Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a")) I don't know a lot about Macros...just enough to try and get by. Sub SAPTime1() ' ' SAPTime1 Macro ' Macro recorded 10/19/2008 by Bud Zeiger czj63c ' ' Keyboard Shortcut: Ctrl+Shift+S ' 'Let's check to see if we have any data or the right data Sheets("SAPTasks").Select Range("a1").Select If ActiveCell.FormulaR1C1 < "Personnel Number" Then MsgBox "Please close workbook, re-open, and paste ZZTaskDB_Disp SAP info into SAPTasks worksheet" Exit Sub End If 'Start of selecting TimeCardData deleting and than re-creating Sheets("TimeCardData").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Application.DisplayAlerts = False Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = "TimeCardData" 'End of selecting TimeCardData deleting and than re-creating 'Start of selecting TimeCardDataSav deleting and than re-creating ' This is a hidden file 'Sheets("TimeCardDataSav").Select 'Application.CutCopyMode = False 'Selection.Delete Shift:=xlUp 'Application.DisplayAlerts = False 'ActiveWindow.SelectedSheets.Delete 'Application.DisplayAlerts = True 'Application.DisplayAlerts = False Sheets.Add Sheets("Sheet2").Select Sheets("Sheet2").Name = "TimeCardDataSav" 'End of selecting TimeCardData deleting and than re-creating 'This next statement turns off the screen updating while the macro is running Application.ScreenUpdating = False Dim br As Long 'Select SAPTasks and count the number of active rows Sheets("SAPTasks").Select Cells.Select br = Cells(Rows.Count, "b").End(xlUp).Row 'Select and enter br in an empty cell 'Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("E2") _ ' , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ ' False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _ ' DataOption2:=xlSortNormal Range("A2").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers 'BEGIN - Ensure that column A is formated to numeric Columns("A:A").Select Selection.NumberFormat = "0" 'Select and enter 1 in an empty cell Range("R1").Select ActiveCell.FormulaR1C1 = "1" 'Copy the cell Selection.Copy 'Select from first cell to last used cell in column A 'Identifying last used cell is like selecting the 'last cell in column A (65536 or something.) 'Holding the Ctrl key and pressing up arrow to 'find last used cell in the column. Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)).Select 'Paste Special multiply Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False 'END - Ensure that column A is formatted to numeric 'MsgBox br 'Select TimeCardDataSav as we are going to build the UPLOAD file Sheets("TimeCardDataSav").Select Cells.Select ' First Clear all contents of worksheet ActiveSheet.Cells.ClearContents ' Second Add a line that describes the data fields ' Also add the formulas for obtaining the data ' Range("S1").Select 'ActiveCell.FormulaR1C1 = br Cells(1, "a") = "CATS Document Number" Cells(1, "b") = "Person" Cells(1, "c") = "Workdate" Cells(1, "d") = "Time From" Cells(1, "e") = "Time To" Cells(1, "f") = "Receiver WBS Element" Cells(1, "g") = "Absence-Attendance Type" Cells(1, "h") = "Hours" Cells(1, "i") = "Text (40chars)" Cells(1, "j") = "User Field1 (15 Chars)" Cells(1, "k") = "User Field 2 (15chars)" Cells(1, "l") = "User Field 3 (15chars)" Cells(1, "m") = "External Project Task" 'Cells(1, "n") = "Complete (X=complete)" Cells(1, "n") = "Remaining Work (in Hours)" Cells(1, "o") = "Invoice Role" Cells(1, "p") = "Capability" Cells(2, "a") = " " 'ID Cells(2, "b") = "=SAPTasks!A2" 'SAP Personnel Cells(2, "f") = "=SAPTasks!J2" 'SAP WBSe Cells(2, "g") = "2000" Cells(2, "i") = "=SAPTasks!e2" 'Task name Cells(2, "k") = "=SAPTasks!o2" 'Resource name Cells(2, "l") = "=SAPTasks!p2" 'EDS NET ID Cells(2, "m") = "=SAPTasks!c2" 'External Project Task ' The next matching formula concatenates the number of records in saptasks for knowing when to stop on the fill down 'Cells(2, "k") = "=IF($b$2:$b$" & br & "="""","""",(INDEX(Personnel!$B$1:$B$1000,MATCH($b $2:$b" & br & ",Personnel!$A$1:$A$1000,0))))" ' Thirdly Auto fill down for the number rows we obtained from the SAP-Simulation Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a")) Cells(2, "b").AutoFill Destination:=Range(Cells(2, "b"), Cells(br, "b")) Cells(2, "f").AutoFill Destination:=Range(Cells(2, "f"), Cells(br, "f")) Cells(2, "g").AutoFill Destination:=Range(Cells(2, "g"), Cells(br, "g")) Cells(2, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k")) Cells(2, "i").AutoFill Destination:=Range(Cells(2, "i"), Cells(br, "i")) Cells(2, "l").AutoFill Destination:=Range(Cells(2, "l"), Cells(br, "l")) Cells(2, "m").AutoFill Destination:=Range(Cells(2, "m"), Cells(br, "m")) 'Wrapping text Begin Range("D1").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("E1").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("G1").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("N1").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("O1").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("A1").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With ActiveWindow.SmallScroll ToRight:=2 Range("C2").Select Columns("A:A").ColumnWidth = 3.5 'Columns("B:B").ColumnWidth = 11 Columns("C:C").ColumnWidth = 8.5 Columns("D:D").ColumnWidth = 2.5 Columns("E:E").ColumnWidth = 2.5 Columns("F:F").ColumnWidth = 24 Columns("g:g").ColumnWidth = 6 Columns("h:h").ColumnWidth = 6 Columns("i:i").ColumnWidth = 45 Columns("k:k").ColumnWidth = 22 Columns("m:m").ColumnWidth = 19 Sheets("TimeCardDataSav").Select Cells.Select Selection.Copy Sheets("TimeCardData").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Columns("A:A").ColumnWidth = 3.5 'Columns("B:B").ColumnWidth = 11 Columns("C:C").ColumnWidth = 8.5 Columns("D:D").ColumnWidth = 2.5 Columns("E:E").ColumnWidth = 2.5 Columns("F:F").ColumnWidth = 24 Columns("g:g").ColumnWidth = 6 Columns("h:h").ColumnWidth = 6 Columns("i:i").ColumnWidth = 45 Columns("k:k").ColumnWidth = 22 Columns("m:m").ColumnWidth = 19 'Start of selecting TimeCardData deleting and than re-creating Sheets("SAPTasks").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Application.DisplayAlerts = False Sheets.Add Sheets("Sheet3").Select Sheets("Sheet3").Name = "SAPTasks" 'End of selecting TimeCardData deleting and than re-creating 'Hiding the saved file 'Sheets("TimeCardDataSav").Select 'ActiveWindow.SelectedSheets.Visible = False 'Start of selecting TimeCardData deleting and than re-creating Sheets("TimeCardDataSav").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '1004': AutoFill method of Range class failed | Excel Discussion (Misc queries) | |||
Run-time error '1004': AutoFill method of Range class failed | Excel Discussion (Misc queries) | |||
Run-time error '1004': Insert method of Range class failed | Excel Programming | |||
Run-time error '1004': Select method of Sheets class failed | Excel Programming | |||
Run-time error '1004' PasteSpecial Method of Range Class Failed | Excel Programming |