Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 - again
Running the following code yields a 1004 error at the noted line. I've tried everything I can think of - any ideas, please?
Option Explicit Sub Create_Telephone_List() Dim i, k, m As Long Dim n, j As Integer Dim s, r, t, u, v, w, x As String Dim ws1, ws2 As Worksheet Application.ScreenUpdating = False Set ws1 = Sheets("Telephone List") Set ws2 = Sheets("Telephone Directory") k = 0 'Locater Row for paste n = 1 'Column number For i = 1 To 26 ws1.Activate ws1.Cells(3, n).Select j = ActiveCell.CurrentRegion.Rows.Count 'number of rows ws1.Range(Cells(2, n), Cells(j + 3, n + 1)).Select 'ERROR 1004 Selection.Copy ws2.Select k = k + j + 1 Cells(k, 1).Select ActiveSheet.Paste n = n + 3 Next End Sub Thanks, Jim Berglund |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 - again
I can't replicate this on my PC. Isn't that always the way! Some
possibilities: You can get error 1004 when selecting a range if the code is being called by a button - or if some object on the sheet is selected. The code has to explicitly deselect the button/object before selecting other ranges. However, in this case you have some range selection code in an earlier line so that may not be it. Is it possible you have some unusual values for j showing up? What happens if you replace the j and n in this line with values (e.g. ws1.Range(Cells(2, 3), Cells(2 + 3, 2 + 1)).Select If that works, then the problem must be with j or n, and you can track it down from there. David Sauder On Apr 15, 1:06 pm, "Jim Berglund" wrote: Running the following code yields a 1004 error at the noted line. I've tried everything I can think of - any ideas, please? Option Explicit Sub Create_Telephone_List() Dim i, k, m As Long Dim n, j As Integer Dim s, r, t, u, v, w, x As String Dim ws1, ws2 As Worksheet Application.ScreenUpdating = False Set ws1 = Sheets("Telephone List") Set ws2 = Sheets("Telephone Directory") k = 0 'Locater Row for paste n = 1 'Column number For i = 1 To 26 ws1.Activate ws1.Cells(3, n).Select j = ActiveCell.CurrentRegion.Rows.Count 'number of rows ws1.Range(Cells(2, n), Cells(j + 3, n + 1)).Select 'ERROR 1004 Selection.Copy ws2.Select k = k + j + 1 Cells(k, 1).Select ActiveSheet.Paste n = n + 3 Next End Sub Thanks, Jim Berglund |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 - again
I have j & n Dimensioned as Integers, and both have been initialized with integer values. I tried your suggestion of substituting values
ws1.Range(Cells(2, 1), Cells(22, 2)).Select but it still won't work. Could it be a problem with Excel? Jim "David Sauder" wrote in message ... I can't replicate this on my PC. Isn't that always the way! Some possibilities: You can get error 1004 when selecting a range if the code is being called by a button - or if some object on the sheet is selected. The code has to explicitly deselect the button/object before selecting other ranges. However, in this case you have some range selection code in an earlier line so that may not be it. Is it possible you have some unusual values for j showing up? What happens if you replace the j and n in this line with values (e.g. ws1.Range(Cells(2, 3), Cells(2 + 3, 2 + 1)).Select If that works, then the problem must be with j or n, and you can track it down from there. David Sauder On Apr 15, 1:06 pm, "Jim Berglund" wrote: Running the following code yields a 1004 error at the noted line. I've tried everything I can think of - any ideas, please? Option Explicit Sub Create_Telephone_List() Dim i, k, m As Long Dim n, j As Integer Dim s, r, t, u, v, w, x As String Dim ws1, ws2 As Worksheet Application.ScreenUpdating = False Set ws1 = Sheets("Telephone List") Set ws2 = Sheets("Telephone Directory") k = 0 'Locater Row for paste n = 1 'Column number For i = 1 To 26 ws1.Activate ws1.Cells(3, n).Select j = ActiveCell.CurrentRegion.Rows.Count 'number of rows ws1.Range(Cells(2, n), Cells(j + 3, n + 1)).Select 'ERROR 1004 Selection.Copy ws2.Select k = k + j + 1 Cells(k, 1).Select ActiveSheet.Paste n = n + 3 Next End Sub Thanks, Jim Berglund |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 - again
Well, I'm out of ideas. If you want, you could send me a copy (after
taking out any confidential information, of course), and I'll see if it works on my machine. dsauderathiddenlogic.com David On Apr 16, 9:31 am, "Jim Berglund" wrote: I have j & n Dimensioned as Integers, and both have been initialized with integer values. I tried your suggestion of substituting values ws1.Range(Cells(2, 1), Cells(22, 2)).Select but it still won't work. Could it be a problem with Excel? Jim "David Sauder" wrote in ... I can't replicate this on my PC. Isn't that always the way! Some possibilities: You can get error 1004 when selecting a range if the code is being called by a button - or if some object on the sheet is selected. The code has to explicitly deselect the button/object before selecting other ranges. However, in this case you have some range selection code in an earlier line so that may not be it. Is it possible you have some unusual values for j showing up? What happens if you replace the j and n in this line with values (e.g. ws1.Range(Cells(2, 3), Cells(2 + 3, 2 + 1)).Select If that works, then the problem must be with j or n, and you can track it down from there. David Sauder On Apr 15, 1:06 pm, "Jim Berglund" wrote: Running the following code yields a 1004 error at the noted line. I've tried everything I can think of - any ideas, please? Option Explicit Sub Create_Telephone_List() Dim i, k, m As Long Dim n, j As Integer Dim s, r, t, u, v, w, x As String Dim ws1, ws2 As Worksheet Application.ScreenUpdating = False Set ws1 = Sheets("Telephone List") Set ws2 = Sheets("Telephone Directory") k = 0 'Locater Row for paste n = 1 'Column number For i = 1 To 26 ws1.Activate ws1.Cells(3, n).Select j = ActiveCell.CurrentRegion.Rows.Count 'number of rows ws1.Range(Cells(2, n), Cells(j + 3, n + 1)).Select 'ERROR 1004 Selection.Copy ws2.Select k = k + j + 1 Cells(k, 1).Select ActiveSheet.Paste n = n + 3 Next End Sub Thanks, Jim Berglund |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 - again
Qualify your ranges -- including the cells().
ws1.Range(ws1.Cells(2, 1), ws1.Cells(22, 2)).Select But since ws1 has to be active to select a range, maybe: with ws1 .select .range(.cells(2,1),.cells(22,2)).select end with ps. These newsgroups are plain text. Lots of people will ignore messages in HTML/RTF or messages that include attachments. (I know I ignored your message until today--those HTML messages are hard to read!) Jim Berglund wrote: I have j & n Dimensioned as Integers, and both have been initialized with integer values. I tried your suggestion of substituting values ws1.Range(Cells(2, 1), Cells(22, 2)).Select but it still won't work. Could it be a problem with Excel? Jim "David Sauder" wrote in message ... I can't replicate this on my PC. Isn't that always the way! Some possibilities: You can get error 1004 when selecting a range if the code is being called by a button - or if some object on the sheet is selected. The code has to explicitly deselect the button/object before selecting other ranges. However, in this case you have some range selection code in an earlier line so that may not be it. Is it possible you have some unusual values for j showing up? What happens if you replace the j and n in this line with values (e.g. ws1.Range(Cells(2, 3), Cells(2 + 3, 2 + 1)).Select If that works, then the problem must be with j or n, and you can track it down from there. David Sauder On Apr 15, 1:06 pm, "Jim Berglund" wrote: Running the following code yields a 1004 error at the noted line. I've tried everything I can think of - any ideas, please? Option Explicit Sub Create_Telephone_List() Dim i, k, m As Long Dim n, j As Integer Dim s, r, t, u, v, w, x As String Dim ws1, ws2 As Worksheet Application.ScreenUpdating = False Set ws1 = Sheets("Telephone List") Set ws2 = Sheets("Telephone Directory") k = 0 'Locater Row for paste n = 1 'Column number For i = 1 To 26 ws1.Activate ws1.Cells(3, n).Select j = ActiveCell.CurrentRegion.Rows.Count 'number of rows ws1.Range(Cells(2, n), Cells(j + 3, n + 1)).Select 'ERROR 1004 Selection.Copy ws2.Select k = k + j + 1 Cells(k, 1).Select ActiveSheet.Paste n = n + 3 Next End Sub Thanks, Jim Berglund -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Run Time Error 1004: Application or Object Defined Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |