Interuption with no explaination :/

[Guest]

Hi. I've had this error a couple of times, and can't figure it out.
I write a macro and when running it I get "Code Execution has been
interupted" and when I debug it's on random lines...

Today i've wrote a macro, which I'll attach at the bottom. The macro worked
great, but since I don't need to watch it running and it'd be quicker I
turned screen updating off. Now it errors.

The error comes up mostly on "If n = 0 Then" and looking at it n = 1 at that
point, but half the time it will be a random line of code that seemingly
looks fine to me...

dat is yesterday's date from the workbook, so it deletes all lines with
previous date, and if column 4 is Z then its not an appointment and wants
deleting. day1 and day2 are files with a name based on today's date, so are
variable.

As far as I can tell theres nothing wrong with this code. I certainly get
none of the normal errors when I write something shoddy, just the interuption
thing... i'm not pressing any keys or anything while it runs it just seems to
interupt itself for no reason I can see...

Sub open_reports()
Dim day1 As Variant
Dim day2 As Variant
Dim i As Variant
Dim dat As Variant
Dim n As Variant

Application.ScreenUpdating = False

Sheets("Calc").Select

day1 = Range("B19").Value
day2 = Range("C19").Value
dat = Range("F2").Value

Workbooks.Open Filename:= _
day1
Sheets("diary").Select
Windows("Appointments Booked.xls").Activate
Workbooks.Open Filename:= _
day2
Sheets("diary").Select
Cells.Select
Selection.Copy
Windows("Appointments Booked.xls").Activate
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Windows("Appointments Booked.xls").Activate
Range("B2").Select

i = 2

Do Until i = 50000

n = 0

If Cells(i, 4) = "Z" Then
n = 1
Else
End If

If Cells(i, 5) < dat Then
n = 1
Else
End If

If n = 1 Then Rows(i).Delete
If n = 0 Then i = i + 1


Loop
End Sub

 

[Jim Cone]

If you will go thru your code and specify the Workbook name and the
Worksheet name for every reference then I may be able to help.
For example...
Dim WBday1 As Excel.Workbook
Dim WBday2 As Excel.Workbook

day2 = Workbooks("SomeName").Sheets("Calc").Range("C19").Value

Set WBday2 = Workbooks.Open(Filename:=day2)
WBday2.Sheets("diary").UsedRange.Copy
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"PaulW" <[email protected]>
wrote in message
Hi. I've had this error a couple of times, and can't figure it out.
I write a macro and when running it I get "Code Execution has been
interupted" and when I debug it's on random lines...

Today i've wrote a macro, which I'll attach at the bottom. The macro worked
great, but since I don't need to watch it running and it'd be quicker I
turned screen updating off. Now it errors.

The error comes up mostly on "If n = 0 Then" and looking at it n = 1 at that
point, but half the time it will be a random line of code that seemingly
looks fine to me...

dat is yesterday's date from the workbook, so it deletes all lines with
previous date, and if column 4 is Z then its not an appointment and wants
deleting. day1 and day2 are files with a name based on today's date, so are
variable.

As far as I can tell theres nothing wrong with this code. I certainly get
none of the normal errors when I write something shoddy, just the interuption
thing... i'm not pressing any keys or anything while it runs it just seems to
interupt itself for no reason I can see...

Sub open_reports()
Dim day1 As Variant
Dim day2 As Variant
Dim i As Variant
Dim dat As Variant
Dim n As Variant

Application.ScreenUpdating = False

Sheets("Calc").Select

day1 = Range("B19").Value
day2 = Range("C19").Value
dat = Range("F2").Value

Workbooks.Open Filename:= _
day1
Sheets("diary").Select
Windows("Appointments Booked.xls").Activate
Workbooks.Open Filename:= _
day2
Sheets("diary").Select
Cells.Select
Selection.Copy
Windows("Appointments Booked.xls").Activate
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Windows("Appointments Booked.xls").Activate
Range("B2").Select

i = 2

Do Until i = 50000

n = 0

If Cells(i, 4) = "Z" Then
n = 1
Else
End If

If Cells(i, 5) < dat Then
n = 1
Else
End If

If n = 1 Then Rows(i).Delete
If n = 0 Then i = i + 1


Loop
End Sub

 

[Guest]

I'd try but I'd be afraid that i'd get something wrong. Being self taught
this is the way I know how to do this.

I've tried in the past to set variables as things other than variants and
tried to use
With sheets("Name")
but they've both failed, and not knowing how to get it right I just did
something different that did work, like simply having variables as variants,
and selecting the page.

Hopefully this will help..

Ignore day1, this is for a part of the macro unwritten yet, getting the
first part correct first.
The sheet in question is called "Appointments Booked.xls" and the name of a
spreadsheet is in the "Calc" sheet, in cell "C19".
This spreadsheet is opened, and the whole thing is copied, the Appointments
Booked spreadsheet is activated and then "Sheet3", thats where the
information is pasted.
Basically until this point, all thats happened is two workbooks are opened,
and one of them (the diary sheet) is copied into the original workbook.

Everything after that is done in "Sheet3" (the copied information) every row
that contains a "Z" in column 4 (D) or if the date in column 5 (E) is less
than yesterdays date, then that row wants deleting.

These sheets are a list of appointments booked into a diary. Each day a
macro I made saves the information into a spreadsheet, and uses this
information to make another sheet, and then a pivot table, so all these
sheets end up with 3 sheets, "diary", "diary(2)" and "Sheet1". These sheets
are then saved as diaryDDMMYY.

This macro wants to open today's sheet, and yesterday's. Looking at the
original unaltered data (diary sheet) and find what's on Today's sheet that
wasn't on Yesterday's, therefore telling me which appointments were booked
yesterday.
Column 4 (D) has the location of the appointment, and column 5 (E) has the
date of the appointment.
Since this list of appointments is a good 40,000 rows, some of these can be
easily deleted, especially since it shows the last 2 years worth of
information.
The comparison part hasn't been done yet, this macro simply deletes old
appointments, and appointments with a location of "Z" which is an empty
space...

 

[Jim Cone]

When using more than one sheet... specify the sheet name.
When using more than one workbook...specify the workbook name.
When deleting cells/rows... do it from the bottom up.
Suggest you review "Data Type Summary" in Excel help.
Note the use of dots "." in the "With" code section.
The following is untested but it should come close...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(color sort, compare, unique, thesaurus and other add-ins)

'------------
Sub open_reports()
Dim day1 As Variant
Dim day2 As Variant
Dim dat As Variant
Dim i As Long

Dim WB_apbk As Excel.Workbook
Dim WB_day2 As Excel.Workbook

Application.ScreenUpdating = False
Set WB_apbk = Workbooks("Appointments Booked.xls")

day1 = WB_apbk.Sheets("Calc").Range("B19").Value
day2 = WB_apbk.Sheets("Calc").Range("C19").Value
dat = WB_apbk.Sheets("Calc").Range("F2").Value

Set WB_day2 = Workbooks.Open(Filename:=day2)
WB_day2.Sheets("diary").Cells.Copy Destination:=WB_apbk.Sheets("Sheet3").Range("A1")

WB_apbk.Activate
With WB_apbk.Sheets("Sheet3")
.Select
.DisplayPageBreaks = False
For i = 50000 To 2 Step -1
If .Cells(i, 4) = "Z" Or .Cells(i, 5) < dat Then
.Rows(i).Delete
End If
Next 'i
.Range("B2").Select
End With
Application.ScreenUpdating = True
Set WB_apbk = Nothing
Set WB_day2 = Nothing
End Sub
'-----------


"PaulW" <[email protected]>
wrote in message
I'd try but I'd be afraid that i'd get something wrong. Being self taught
this is the way I know how to do this.

I've tried in the past to set variables as things other than variants and
tried to use
With sheets("Name")
but they've both failed, and not knowing how to get it right I just did
something different that did work, like simply having variables as variants,
and selecting the page.

Hopefully this will help..

Ignore day1, this is for a part of the macro unwritten yet, getting the
first part correct first.
The sheet in question is called "Appointments Booked.xls" and the name of a
spreadsheet is in the "Calc" sheet, in cell "C19".
This spreadsheet is opened, and the whole thing is copied, the Appointments
Booked spreadsheet is activated and then "Sheet3", thats where the
information is pasted.
Basically until this point, all thats happened is two workbooks are opened,
and one of them (the diary sheet) is copied into the original workbook.

Everything after that is done in "Sheet3" (the copied information) every row
that contains a "Z" in column 4 (D) or if the date in column 5 (E) is less
than yesterdays date, then that row wants deleting.

These sheets are a list of appointments booked into a diary. Each day a
macro I made saves the information into a spreadsheet, and uses this
information to make another sheet, and then a pivot table, so all these
sheets end up with 3 sheets, "diary", "diary(2)" and "Sheet1". These sheets
are then saved as diaryDDMMYY.

This macro wants to open today's sheet, and yesterday's. Looking at the
original unaltered data (diary sheet) and find what's on Today's sheet that
wasn't on Yesterday's, therefore telling me which appointments were booked
yesterday.
Column 4 (D) has the location of the appointment, and column 5 (E) has the
date of the appointment.
Since this list of appointments is a good 40,000 rows, some of these can be
easily deleted, especially since it shows the last 2 years worth of
information.
The comparison part hasn't been done yet, this macro simply deletes old
appointments, and appointments with a location of "Z" which is an empty
space...