Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro dies in the middle of execution
Hi,
I'm running a macro (code below) that separates information about employees into different worksheets according to their role on a particular project. The master sheet has about 4200 rows of information, and there are 15 different roles. My problem is that the macro stops executing at about line 1337 on the master data page. It deletes this row and stops executing. Is there a maximum run time, or is there something funky in my code? I am obviously only getting through about a third of my data before this macro quits and it's pretty frustrating. Thanks Sub Copy_Select() Application.ScreenUpdating = False 'Copy and Paste rows from Master List according to the ESM category 'Declare Variables Dim stRole As String Dim inMasterRow As Integer 'This is the active row on the Master Sheet Dim inRow As Integer 'This is the variable used to determine the blank row on the working sheet Dim BegAddress 'used to deliver beginning cell address to range functions Dim EndAddress 'used to deliver end cell address to range functions Dim stType As String 'Used to control the Do-While loop Dim stBlank As String ' Used to control the do-while loop that determines blank rows 'Select first row of the Master worksheet Worksheets("Engineer Roles Table").Select inMasterRow = 2 stType = Cells(inMasterRow, 1).Value 'Used to initiate the Do-While loop Do While stType < "" Worksheets("Engineer Roles Table").Select stRole = Cells(inMasterRow, 5).Value stType = Cells(inMasterRow, 1).Value BegAddress = Cells(inMasterRow, 1).Address EndAddress = Cells(inMasterRow, 9).Address 'Copy data (Select row) Range(BegAddress & ":" & EndAddress).Copy 'Determine which sheet to paste data according to ESM Status (Select Case) 'Select proper worksheet according to status 'MsgBox ESMStatus 'for auditing If stRole < "" Then Worksheets(stRole).Select 'Find first empty row on the selected sheet inRow = 1 stBlank = Cells(inRow, 1).Value Do While stBlank < "" inRow = inRow + 1 stBlank = Cells(inRow, 1).Value Loop End If 'Paste Data BegAddress = Cells(inRow, 1).Address EndAddress = Cells(inRow, 9).Address Range(BegAddress & ":" & EndAddress).PasteSpecial 'Go to the next row (repeat the ESMStatus Do-While loop until finished) inMasterRow = inMasterRow + 1 Loop Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro dies in the middle of execution
Jon,
Are either inMasterRow or inRow ever higher than 32,000 or so? If so, declaring them as longs might solve it. hth, Doug Glancy "JonR" wrote in message ... Hi, I'm running a macro (code below) that separates information about employees into different worksheets according to their role on a particular project. The master sheet has about 4200 rows of information, and there are 15 different roles. My problem is that the macro stops executing at about line 1337 on the master data page. It deletes this row and stops executing. Is there a maximum run time, or is there something funky in my code? I am obviously only getting through about a third of my data before this macro quits and it's pretty frustrating. Thanks Sub Copy_Select() Application.ScreenUpdating = False 'Copy and Paste rows from Master List according to the ESM category 'Declare Variables Dim stRole As String Dim inMasterRow As Integer 'This is the active row on the Master Sheet Dim inRow As Integer 'This is the variable used to determine the blank row on the working sheet Dim BegAddress 'used to deliver beginning cell address to range functions Dim EndAddress 'used to deliver end cell address to range functions Dim stType As String 'Used to control the Do-While loop Dim stBlank As String ' Used to control the do-while loop that determines blank rows 'Select first row of the Master worksheet Worksheets("Engineer Roles Table").Select inMasterRow = 2 stType = Cells(inMasterRow, 1).Value 'Used to initiate the Do-While loop Do While stType < "" Worksheets("Engineer Roles Table").Select stRole = Cells(inMasterRow, 5).Value stType = Cells(inMasterRow, 1).Value BegAddress = Cells(inMasterRow, 1).Address EndAddress = Cells(inMasterRow, 9).Address 'Copy data (Select row) Range(BegAddress & ":" & EndAddress).Copy 'Determine which sheet to paste data according to ESM Status (Select Case) 'Select proper worksheet according to status 'MsgBox ESMStatus 'for auditing If stRole < "" Then Worksheets(stRole).Select 'Find first empty row on the selected sheet inRow = 1 stBlank = Cells(inRow, 1).Value Do While stBlank < "" inRow = inRow + 1 stBlank = Cells(inRow, 1).Value Loop End If 'Paste Data BegAddress = Cells(inRow, 1).Address EndAddress = Cells(inRow, 9).Address Range(BegAddress & ":" & EndAddress).PasteSpecial 'Go to the next row (repeat the ESMStatus Do-While loop until finished) inMasterRow = inMasterRow + 1 Loop Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro dies in the middle of execution
they shouldn't be, but that seemed to do the trick.
I am still getting row 1337 deleted form the master table. Any clue why this is happening? "Doug Glancy" wrote: Jon, Are either inMasterRow or inRow ever higher than 32,000 or so? If so, declaring them as longs might solve it. hth, Doug Glancy "JonR" wrote in message ... Hi, I'm running a macro (code below) that separates information about employees into different worksheets according to their role on a particular project. The master sheet has about 4200 rows of information, and there are 15 different roles. My problem is that the macro stops executing at about line 1337 on the master data page. It deletes this row and stops executing. Is there a maximum run time, or is there something funky in my code? I am obviously only getting through about a third of my data before this macro quits and it's pretty frustrating. Thanks Sub Copy_Select() Application.ScreenUpdating = False 'Copy and Paste rows from Master List according to the ESM category 'Declare Variables Dim stRole As String Dim inMasterRow As Integer 'This is the active row on the Master Sheet Dim inRow As Integer 'This is the variable used to determine the blank row on the working sheet Dim BegAddress 'used to deliver beginning cell address to range functions Dim EndAddress 'used to deliver end cell address to range functions Dim stType As String 'Used to control the Do-While loop Dim stBlank As String ' Used to control the do-while loop that determines blank rows 'Select first row of the Master worksheet Worksheets("Engineer Roles Table").Select inMasterRow = 2 stType = Cells(inMasterRow, 1).Value 'Used to initiate the Do-While loop Do While stType < "" Worksheets("Engineer Roles Table").Select stRole = Cells(inMasterRow, 5).Value stType = Cells(inMasterRow, 1).Value BegAddress = Cells(inMasterRow, 1).Address EndAddress = Cells(inMasterRow, 9).Address 'Copy data (Select row) Range(BegAddress & ":" & EndAddress).Copy 'Determine which sheet to paste data according to ESM Status (Select Case) 'Select proper worksheet according to status 'MsgBox ESMStatus 'for auditing If stRole < "" Then Worksheets(stRole).Select 'Find first empty row on the selected sheet inRow = 1 stBlank = Cells(inRow, 1).Value Do While stBlank < "" inRow = inRow + 1 stBlank = Cells(inRow, 1).Value Loop End If 'Paste Data BegAddress = Cells(inRow, 1).Address EndAddress = Cells(inRow, 9).Address Range(BegAddress & ":" & EndAddress).PasteSpecial 'Go to the next row (repeat the ESMStatus Do-While loop until finished) inMasterRow = inMasterRow + 1 Loop Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro dies in the middle of execution
You might consider inserting a trap in your code for row 1337:
If inMasterRow = 1337 then MsgBox "Trap for row 1337" End If When the message box comes up, hit Ctrl+BreakDebug. Then step through your code and try to identify *where* things go wrong, then *why*. Remove (or just comment out, you might need it again) the trap when done. HTH, -- George Nicholson Remove 'Junk' from return address. "JonR" wrote in message ... they shouldn't be, but that seemed to do the trick. I am still getting row 1337 deleted form the master table. Any clue why this is happening? "Doug Glancy" wrote: Jon, Are either inMasterRow or inRow ever higher than 32,000 or so? If so, declaring them as longs might solve it. hth, Doug Glancy "JonR" wrote in message ... Hi, I'm running a macro (code below) that separates information about employees into different worksheets according to their role on a particular project. The master sheet has about 4200 rows of information, and there are 15 different roles. My problem is that the macro stops executing at about line 1337 on the master data page. It deletes this row and stops executing. Is there a maximum run time, or is there something funky in my code? I am obviously only getting through about a third of my data before this macro quits and it's pretty frustrating. Thanks Sub Copy_Select() Application.ScreenUpdating = False 'Copy and Paste rows from Master List according to the ESM category 'Declare Variables Dim stRole As String Dim inMasterRow As Integer 'This is the active row on the Master Sheet Dim inRow As Integer 'This is the variable used to determine the blank row on the working sheet Dim BegAddress 'used to deliver beginning cell address to range functions Dim EndAddress 'used to deliver end cell address to range functions Dim stType As String 'Used to control the Do-While loop Dim stBlank As String ' Used to control the do-while loop that determines blank rows 'Select first row of the Master worksheet Worksheets("Engineer Roles Table").Select inMasterRow = 2 stType = Cells(inMasterRow, 1).Value 'Used to initiate the Do-While loop Do While stType < "" Worksheets("Engineer Roles Table").Select stRole = Cells(inMasterRow, 5).Value stType = Cells(inMasterRow, 1).Value BegAddress = Cells(inMasterRow, 1).Address EndAddress = Cells(inMasterRow, 9).Address 'Copy data (Select row) Range(BegAddress & ":" & EndAddress).Copy 'Determine which sheet to paste data according to ESM Status (Select Case) 'Select proper worksheet according to status 'MsgBox ESMStatus 'for auditing If stRole < "" Then Worksheets(stRole).Select 'Find first empty row on the selected sheet inRow = 1 stBlank = Cells(inRow, 1).Value Do While stBlank < "" inRow = inRow + 1 stBlank = Cells(inRow, 1).Value Loop End If 'Paste Data BegAddress = Cells(inRow, 1).Address EndAddress = Cells(inRow, 9).Address Range(BegAddress & ":" & EndAddress).PasteSpecial 'Go to the next row (repeat the ESMStatus Do-While loop until finished) inMasterRow = inMasterRow + 1 Loop Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OLE link between word and excel dies | New Users to Excel | |||
Macro Execution | Excel Discussion (Misc queries) | |||
Macro Execution Stopped | Excel Programming | |||
Conditional macro execution | Excel Programming | |||
Select a cell in the middle of a macro execution | Excel Programming |