Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill Funtion Error
I'm not an experienced VBA programmer and need someone's help. The code
below comes from a recorded macro, with some ideas from the VBA help files, and I've attempted to modify it to be more generic and not associated with a specific row or sheet. After modifying a cell's formula to reflect a new sheet name, I'm attempting to copy it across several columns, as follows (newRow is Dim'ed as an integer and is valid when I run the code): newFormula = "='" & sheetName & "'!K$2" Cells(newRow, 4).formula = newFormula ActiveSheet.Cells(newRow, 4).Select Selection.AutoFill Destination:=Range(Cells(newRow, 5), Cells(newRow, 9)), Type:=xlFillDefault I get a run-time error 1004 - Autofill method of Range class failed. If I debug the code and hover the cursor over the various elements, I get "Cells(newRow, 5)=Error 2023". What am I doing incorrectly and where can I find the definitions of the reported error codes? I've searched help and searched the MS website without success. TIA DJM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill Funtion Error
The destination range must include the source range. So
Sub AC() sheetname = "ABCD" newRow = 6 newFormula = "='" & sheetname & "'!K$2" Cells(newRow, 4).Formula = newFormula ActiveSheet.Cells(newRow, 4).Select Selection.AutoFill Destination:=Range(Selection, _ Cells(newRow, 9)), Type:=xlFillDefault End Sub ? cvErr(xlErrRef) Error 2023 so this is the worksheet reference error. -- Regards, Tom Ogilvy "DM" wrote in message ... I'm not an experienced VBA programmer and need someone's help. The code below comes from a recorded macro, with some ideas from the VBA help files, and I've attempted to modify it to be more generic and not associated with a specific row or sheet. After modifying a cell's formula to reflect a new sheet name, I'm attempting to copy it across several columns, as follows (newRow is Dim'ed as an integer and is valid when I run the code): newFormula = "='" & sheetName & "'!K$2" Cells(newRow, 4).formula = newFormula ActiveSheet.Cells(newRow, 4).Select Selection.AutoFill Destination:=Range(Cells(newRow, 5), Cells(newRow, 9)), Type:=xlFillDefault I get a run-time error 1004 - Autofill method of Range class failed. If I debug the code and hover the cursor over the various elements, I get "Cells(newRow, 5)=Error 2023". What am I doing incorrectly and where can I find the definitions of the reported error codes? I've searched help and searched the MS website without success. TIA DJM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill Funtion Error
Tom, many thanks. I guess if I had read the help just a little more
carefully, I would have seen it :-) Now that it works, I've noticed another annoyance. The formating of the source cell is being copied so I will need to use the PasteSpecial method instead, specifying the xlPasteFormulas type. Does this work the same way wrt range? I realize I'll need to Copy before PasteSpecial. DJM "Tom Ogilvy" wrote in message ... The destination range must include the source range. So Sub AC() sheetname = "ABCD" newRow = 6 newFormula = "='" & sheetname & "'!K$2" Cells(newRow, 4).Formula = newFormula ActiveSheet.Cells(newRow, 4).Select Selection.AutoFill Destination:=Range(Selection, _ Cells(newRow, 9)), Type:=xlFillDefault End Sub ? cvErr(xlErrRef) Error 2023 so this is the worksheet reference error. -- Regards, Tom Ogilvy "DM" wrote in message ... I'm not an experienced VBA programmer and need someone's help. The code below comes from a recorded macro, with some ideas from the VBA help files, and I've attempted to modify it to be more generic and not associated with a specific row or sheet. After modifying a cell's formula to reflect a new sheet name, I'm attempting to copy it across several columns, as follows (newRow is Dim'ed as an integer and is valid when I run the code): newFormula = "='" & sheetName & "'!K$2" Cells(newRow, 4).formula = newFormula ActiveSheet.Cells(newRow, 4).Select Selection.AutoFill Destination:=Range(Cells(newRow, 5), Cells(newRow, 9)), Type:=xlFillDefault I get a run-time error 1004 - Autofill method of Range class failed. If I debug the code and hover the cursor over the various elements, I get "Cells(newRow, 5)=Error 2023". What am I doing incorrectly and where can I find the definitions of the reported error codes? I've searched help and searched the MS website without success. TIA DJM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoFill Funtion Error
No, you only need to include the destination, at the same time, since the
ranges are contiguous, you can include the source range as well. You can actually skip both and just do newFormula = "='" & sheetName & "'!K$2" Range(Cells(newRow, 4), Cells(newRow, 9)).Formula = _ newFormula -- Regards, Tom Ogilvy "DM" wrote in message ... Tom, many thanks. I guess if I had read the help just a little more carefully, I would have seen it :-) Now that it works, I've noticed another annoyance. The formating of the source cell is being copied so I will need to use the PasteSpecial method instead, specifying the xlPasteFormulas type. Does this work the same way wrt range? I realize I'll need to Copy before PasteSpecial. DJM "Tom Ogilvy" wrote in message ... The destination range must include the source range. So Sub AC() sheetname = "ABCD" newRow = 6 newFormula = "='" & sheetname & "'!K$2" Cells(newRow, 4).Formula = newFormula ActiveSheet.Cells(newRow, 4).Select Selection.AutoFill Destination:=Range(Selection, _ Cells(newRow, 9)), Type:=xlFillDefault End Sub ? cvErr(xlErrRef) Error 2023 so this is the worksheet reference error. -- Regards, Tom Ogilvy "DM" wrote in message ... I'm not an experienced VBA programmer and need someone's help. The code below comes from a recorded macro, with some ideas from the VBA help files, and I've attempted to modify it to be more generic and not associated with a specific row or sheet. After modifying a cell's formula to reflect a new sheet name, I'm attempting to copy it across several columns, as follows (newRow is Dim'ed as an integer and is valid when I run the code): newFormula = "='" & sheetName & "'!K$2" Cells(newRow, 4).formula = newFormula ActiveSheet.Cells(newRow, 4).Select Selection.AutoFill Destination:=Range(Cells(newRow, 5), Cells(newRow, 9)), Type:=xlFillDefault I get a run-time error 1004 - Autofill method of Range class failed. If I debug the code and hover the cursor over the various elements, I get "Cells(newRow, 5)=Error 2023". What am I doing incorrectly and where can I find the definitions of the reported error codes? I've searched help and searched the MS website without success. TIA DJM |
Reply |
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) | |||
Autofill & On Error Resume Next | Excel Discussion (Misc queries) | |||
Date Value Funtion in Excel 2003 returns an error, but not in Exce | Excel Worksheet Functions | |||
Autofill Error | Excel Programming | |||
autofill macro has error when there is nothing to fill. | Excel Programming |