Error 1004 - again

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim Berglund - 15 Apr 2008 20:06 GMT

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

Reply to this Message

David Sauder - 16 Apr 2008 02:40 GMT

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

> 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
[quoted text clipped - 26 lines]
> Thanks,
> Jim Berglund

Reply to this Message

Jim Berglund - 16 Apr 2008 16:31 GMT

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

>I can't replicate this on my PC. Isn't that always the way! Some
> possibilities:
[quoted text clipped - 43 lines]
>> Thanks,
>> Jim Berglund

Reply to this Message

David Sauder - 18 Apr 2008 02:11 GMT

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

> I have j & n Dimensioned as Integers, and both have been initialized with integer values. I tried your suggestion of substituting values
>
[quoted text clipped - 52 lines]
> >> Thanks,
> >> Jim Berglund

Reply to this Message

Dave Peterson - 18 Apr 2008 13:40 GMT

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:
>
[quoted text clipped - 56 lines]
> >> Thanks,
> >> Jim Berglund

Signature

Dave Peterson

Reply to this Message