Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interuption with no explaination :/
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interuption with no explaination :/
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interuption with no explaination :/
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" wrote: 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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interuption with no explaination :/
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" 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function explaination need | Excel Worksheet Functions | |||
Formula explaination | Excel Discussion (Misc queries) | |||
Need some help and explaination. Thks. | Excel Programming | |||
Need some help and explaination. Thks. | Excel Programming | |||
Need some help and explaination. Thks. | Excel Programming |