| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofill method of range class failed
Hi,
I have an error on the code below. Here is the line that cause the error message. Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault If I use this (when I use EY6 instead of using cells(...) ) Selection.AutoFill Destination:=ws.Range("A6:EY6"), Type:=xlFillDefault it doesn't give an error but I need to use the cell to find the last column filled in the row. For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then Worksheets(ws.Index).Activate ws.Rows("6:6").Select Selection.Insert Shift:=xlDown Range("A6").Value = "=SUBTOTAL(3,A7:A1999)" ws.Range("A6").Select Selection.Copy ws.Range("A6").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ' Selection.AutoFill Destination:=ws.Range("A6:EY6"), Type:=xlFillDefault Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault 'ws.Range("A6:EY6").Select ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & "").Select End If Next -- Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Autofill method of range class failed
Hi,
It gives an error message (Aplication defined or object defined error) in the line of: ..Cells(6, 1).AutoFill .Range(Cells(6, 1), Cells(6, lastcol)) Any idea why it is giving this error message? -- Thanks "Don Guillett" wrote: For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then with ws lastcol=.cells(6,columns.count).end(xltoleft).colu mn .Rows("6:6").Insert Shift:=xlDown ' .Range("A6").Value = "=SUBTOTAL(3,A7:A1999)" ' .Range("A6").value=.Range("A6").value 'or .Range("A6").value=application.SUBTOTAL(3,.range(" A7:A1999") .Cells(6, 1).AutoFill .Range(cells(6,1),cells(6,lastcol)) end with End If Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Appache" wrote in message ... Hi, I have an error on the code below. Here is the line that cause the error message. Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault If I use this (when I use EY6 instead of using cells(...) ) Selection.AutoFill Destination:=ws.Range("A6:EY6"), Type:=xlFillDefault it doesn't give an error but I need to use the cell to find the last column filled in the row. For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then Worksheets(ws.Index).Activate ws.Rows("6:6").Select Selection.Insert Shift:=xlDown Range("A6").Value = "=SUBTOTAL(3,A7:A1999)" ws.Range("A6").Select Selection.Copy ws.Range("A6").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ' Selection.AutoFill Destination:=ws.Range("A6:EY6"), Type:=xlFillDefault Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault 'ws.Range("A6:EY6").Select ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & "").Select End If Next -- Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Autofill method of range class failed
Basicly what I want to do is to insert a row after 5th row and put a subtotal
formula from the begining to end of 6th row. I need to know the last column of the row in order to avoid to put this formula for the unnecessary cells. I can not use a specific address because every sheet has different last column for 5th row. -- Thanks "Don Guillett" wrote: For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then with ws lastcol=.cells(6,columns.count).end(xltoleft).colu mn .Rows("6:6").Insert Shift:=xlDown ' .Range("A6").Value = "=SUBTOTAL(3,A7:A1999)" ' .Range("A6").value=.Range("A6").value 'or .Range("A6").value=application.SUBTOTAL(3,.range(" A7:A1999") .Cells(6, 1).AutoFill .Range(cells(6,1),cells(6,lastcol)) end with End If Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Appache" wrote in message ... Hi, I have an error on the code below. Here is the line that cause the error message. Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault If I use this (when I use EY6 instead of using cells(...) ) Selection.AutoFill Destination:=ws.Range("A6:EY6"), Type:=xlFillDefault it doesn't give an error but I need to use the cell to find the last column filled in the row. For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then Worksheets(ws.Index).Activate ws.Rows("6:6").Select Selection.Insert Shift:=xlDown Range("A6").Value = "=SUBTOTAL(3,A7:A1999)" ws.Range("A6").Select Selection.Copy ws.Range("A6").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ' Selection.AutoFill Destination:=ws.Range("A6:EY6"), Type:=xlFillDefault Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault 'ws.Range("A6:EY6").Select ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & "").Select End If Next -- Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Autofill method of range class failed
Try this. I changed to find the last column in row 5 instead of 6 and added
a ) for the subtotal line. If this doesn't work, feel free to send your workbook to my address below For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then with ws lastcol=.cells(5,columns.count).end(xltoleft).colu mn .Rows("6:6").Insert Shift:=xlDown .Range("A6").value=application.SUBTOTAL(3,.range(" A7:A1999")) .Cells(6, 1).AutoFill .Range(cells(6,1),cells(6,lastcol)) end with End If Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Appache" wrote in message ... Basicly what I want to do is to insert a row after 5th row and put a subtotal formula from the begining to end of 6th row. I need to know the last column of the row in order to avoid to put this formula for the unnecessary cells. I can not use a specific address because every sheet has different last column for 5th row. -- Thanks "Don Guillett" wrote: For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then with ws lastcol=.cells(6,columns.count).end(xltoleft).colu mn .Rows("6:6").Insert Shift:=xlDown ' .Range("A6").Value = "=SUBTOTAL(3,A7:A1999)" ' .Range("A6").value=.Range("A6").value 'or .Range("A6").value=application.SUBTOTAL(3,.range(" A7:A1999") .Cells(6, 1).AutoFill .Range(cells(6,1),cells(6,lastcol)) end with End If Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Appache" wrote in message ... Hi, I have an error on the code below. Here is the line that cause the error message. Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault If I use this (when I use EY6 instead of using cells(...) ) Selection.AutoFill Destination:=ws.Range("A6:EY6"), Type:=xlFillDefault it doesn't give an error but I need to use the cell to find the last column filled in the row. For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then Worksheets(ws.Index).Activate ws.Rows("6:6").Select Selection.Insert Shift:=xlDown Range("A6").Value = "=SUBTOTAL(3,A7:A1999)" ws.Range("A6").Select Selection.Copy ws.Range("A6").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ' Selection.AutoFill Destination:=ws.Range("A6:EY6"), Type:=xlFillDefault Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault 'ws.Range("A6:EY6").Select ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & "").Select End If Next -- Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Autofill method of range class failed
oops. I was doing only the 1st column. This should do it.
For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then With ws lastcol = .Cells(5, Columns.Count).End(xlToLeft).Column .Rows("6:6").Insert Shift:=xlDown For i = 1 To lastcol .Cells(6, i) = Application.Subtotal _ (3, .Range(.Cells(7, i), .Cells(1999, i))) Next i End With end if next ws -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Try this. I changed to find the last column in row 5 instead of 6 and added a ) for the subtotal line. If this doesn't work, feel free to send your workbook to my address below For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then with ws lastcol=.cells(5,columns.count).end(xltoleft).colu mn .Rows("6:6").Insert Shift:=xlDown .Range("A6").value=application.SUBTOTAL(3,.range(" A7:A1999")) .Cells(6, 1).AutoFill .Range(cells(6,1),cells(6,lastcol)) end with End If Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Appache" wrote in message ... Basicly what I want to do is to insert a row after 5th row and put a subtotal formula from the begining to end of 6th row. I need to know the last column of the row in order to avoid to put this formula for the unnecessary cells. I can not use a specific address because every sheet has different last column for 5th row. -- Thanks "Don Guillett" wrote: For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then with ws lastcol=.cells(6,columns.count).end(xltoleft).colu mn .Rows("6:6").Insert Shift:=xlDown ' .Range("A6").Value = "=SUBTOTAL(3,A7:A1999)" ' .Range("A6").value=.Range("A6").value 'or .Range("A6").value=application.SUBTOTAL(3,.range(" A7:A1999") .Cells(6, 1).AutoFill .Range(cells(6,1),cells(6,lastcol)) end with End If Next -- Don Guillett Microsoft MVP Excel SalesAid Software "Appache" wrote in message ... Hi, I have an error on the code below. Here is the line that cause the error message. Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault If I use this (when I use EY6 instead of using cells(...) ) Selection.AutoFill Destination:=ws.Range("A6:EY6"), Type:=xlFillDefault it doesn't give an error but I need to use the cell to find the last column filled in the row. For Each ws In Worksheets If ws.Name < "National (2G-3G)" And ws.Name < "National (2G)" And ws.Name < "National (3G)" And ws.Name < "National (COW)" And ws.Name < "TI Report (2G-3G)" Then Worksheets(ws.Index).Activate ws.Rows("6:6").Select Selection.Insert Shift:=xlDown Range("A6").Value = "=SUBTOTAL(3,A7:A1999)" ws.Range("A6").Select Selection.Copy ws.Range("A6").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False ' Selection.AutoFill Destination:=ws.Range("A6:EY6"), Type:=xlFillDefault Selection.AutoFill Destination:=ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & ""), Type:=xlFillDefault 'ws.Range("A6:EY6").Select ws.Range("A6:" & Cells(6, Columns.Count).End(xlToLeft).Address & "").Select End If Next -- Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Runtime 1004 error -- insert method of range class failed. | Excel Discussion (Misc queries) | |||
| Run-Time error '1004' : Select method of Range class failed | Excel Discussion (Misc queries) | |||
| Run-time error "1004" Select method of range class failed | Excel Discussion (Misc queries) | |||
| Method 'Range' of object '_global' failed | Excel Discussion (Misc queries) | |||
| runtime error 1004 method range of object '_global failed | Excel Discussion (Misc queries) | |||
Linear Mode
