Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Code hangs second time around
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
tom tom is offline
external usenet poster
  
Posts: 570
Default Code hangs second time around
Hi gang,
The code below runs FINE when I first open Excel and run the macro. However,
if I run the macro a second time, it hangs and I have to press the ESC button
to get it to stop. If I completely close down Excel and re-open the file, the
macro will once again run properly if ran only once. Unfortunately, the only
message I get is that the "code has been interrupted" and it does not show me
which line has the error in it. I'm not even sure which of the two macros it
is hanging on. Any ideas???

Sub Filter()
Dim MyRange As Range
Set MyRange = Sheets("DATA").Range("C17:C23")
For x = 400 To 1 Step -1
myvalue = Cells(x, 36).Value
For Each c In MyRange
If myvalue = c.Value Then
Rows(x).EntireRow.Delete
Exit For
End If
Next
Next
Set MyRange = Sheets("DATA").Range("D17:D18")
For x = 400 To 1 Step -1
myvalue = Cells(x, 31).Value
For Each c In MyRange
If myvalue = c.Value Then
Rows(x).EntireRow.Delete
Exit For
End If
Next
Next
Application.Run "GetOurs"
End Sub
-----------------------------------------------------------------------------------------------
Sub GetOurs()
Application.ScreenUpdating = False
Sheets("Conversions").Visible = True
Sheets("Conversions").Select
Range("I3:I200").Select
Selection.Copy
Sheets("FRMLST").Select
Range("W3:W200").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Conversions").Select
Range("M3:M200").Select
Selection.Copy
Sheets("FRMLST").Select
Range("X3:X200").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("Z1").Select
Selection.AutoFill Destination:=Range("Z1:Z200"), Type:=xlFillDefault
Range("Z1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<"
Columns("Z:Z").Select
Selection.Copy
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("Z1").Select
Selection.AutoFilter
Range("F3").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(R[0]C[-1],R1C27:R250C29,3,0)),"""",VLOOKUP(R[0]C[-1],R1C27:R250C29,3,0))"
Range("G3").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(R[0]C[-2],R1C27:R250C35,9,0)),"""",VLOOKUP(R[0]C[-2],R1C27:R250C35,9,0))"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F50"), Type:=xlFillDefault
Range("F3:F50").Select
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G50"), Type:=xlFillDefault
Range("G3:G50").Select
Range("F1:G2").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[2]C[1]0,""PLEASE CHECK THE FOLLOWING FORMULAS"",""ALL
FORMULAS ARE CURRENT"")"
Range("D1").Select
Range("B4").Select
Sheets("Conversions").Visible = False
Application.ScreenUpdating = True
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Code hangs second time around
I had problems gettting the code to work the first time. Th e functtion
filter doesn't make sense if yu are working with one sheet. I think there
should be two sheets in function filter.

In filter you are going from row 400 to 1, then searching in the middle of
this range, then deleting in this range. Can't be done!

I also found a problem with one of you copy instructions. You are copying
an entire column Z and then trying to paste it starting at cell E3. The last
3 rows of the paste will go off the end off the worksheet. I change the
selection to the following

Range("Z1", Range("Z" & Rows.Count).End(xlUp)).Select





Sub Filter()
Dim MyRange As Range
Set MyRange = Sheets("DATA").Range("C17:C23")
For x = 400 To 1 Step -1
With Sheets("sheet1")
myvalue = .Cells(x, 36).Value
For Each c In MyRange
If myvalue = c.Value Then
.Rows(x).EntireRow.Delete
Exit For
End If
Next
End With
Next
Set MyRange = Sheets("DATA").Range("D17:D18")
For x = 400 To 1 Step -1
With Sheets("sheet1")
myvalue = .Cells(x, 31).Value
For Each c In MyRange
If myvalue = c.Value Then
.Rows(x).EntireRow.Delete
Exit For
End If
Next
End With
Next
Call GetOurs
End Sub

Sub GetOurs()
Application.ScreenUpdating = False
Sheets("Conversions").Visible = True
Sheets("Conversions").Select
Range("I3:I200").Select
Selection.Copy
Sheets("FRMLST").Select
Range("W3:W200").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Conversions").Select
Range("M3:M200").Select
Selection.Copy
Sheets("FRMLST").Select
Range("X3:X200").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("Z1").Select
Selection.AutoFill Destination:=Range("Z1:Z200"), Type:=xlFillDefault
Range("Z1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<"
Range("Z1", Range("Z" & Rows.Count).End(xlUp)).Select
Selection.Copy
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("Z1").Select
Selection.AutoFilter
Range("F3").Select
ActiveCell.FormulaR1C1 =
"=IF(ISNA(VLOOKUP(R[0]C[-1],R1C27:R250C29,3,0)),"""",VLOOKUP(R[0]C[-1],R1C27:R250C29,3,0))"
Range("G3").Select
ActiveCell.FormulaR1C1 =
"=IF(ISNA(VLOOKUP(R[0]C[-2],R1C27:R250C35,9,0)),"""",VLOOKUP(R[0]C[-2],R1C27:R250C35,9,0))"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F50"), Type:=xlFillDefault
Range("F3:F50").Select
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G50"), Type:=xlFillDefault
Range("G3:G50").Select
Range("F1:G2").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[2]C[1]0,""PLEASE CHECK THE FOLLOWING FORMULAS"",""ALL
FORMULAS ARE CURRENT"")"
Range("D1").Select
Range("B4").Select
Sheets("Conversions").Visible = False
Application.ScreenUpdating = True
End Sub


"Tom" wrote:

Hi gang,
The code below runs FINE when I first open Excel and run the macro. However,
if I run the macro a second time, it hangs and I have to press the ESC button
to get it to stop. If I completely close down Excel and re-open the file, the
macro will once again run properly if ran only once. Unfortunately, the only
message I get is that the "code has been interrupted" and it does not show me
which line has the error in it. I'm not even sure which of the two macros it
is hanging on. Any ideas???

Sub Filter()
Dim MyRange As Range
Set MyRange = Sheets("DATA").Range("C17:C23")
For x = 400 To 1 Step -1
myvalue = Cells(x, 36).Value
For Each c In MyRange
If myvalue = c.Value Then
Rows(x).EntireRow.Delete
Exit For
End If
Next
Next
Set MyRange = Sheets("DATA").Range("D17:D18")
For x = 400 To 1 Step -1
myvalue = Cells(x, 31).Value
For Each c In MyRange
If myvalue = c.Value Then
Rows(x).EntireRow.Delete
Exit For
End If
Next
Next
Application.Run "GetOurs"
End Sub
-----------------------------------------------------------------------------------------------
Sub GetOurs()
Application.ScreenUpdating = False
Sheets("Conversions").Visible = True
Sheets("Conversions").Select
Range("I3:I200").Select
Selection.Copy
Sheets("FRMLST").Select
Range("W3:W200").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Conversions").Select
Range("M3:M200").Select
Selection.Copy
Sheets("FRMLST").Select
Range("X3:X200").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("Z1").Select
Selection.AutoFill Destination:=Range("Z1:Z200"), Type:=xlFillDefault
Range("Z1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<"
Columns("Z:Z").Select
Selection.Copy
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("Z1").Select
Selection.AutoFilter
Range("F3").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(R[0]C[-1],R1C27:R250C29,3,0)),"""",VLOOKUP(R[0]C[-1],R1C27:R250C29,3,0))"
Range("G3").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(R[0]C[-2],R1C27:R250C35,9,0)),"""",VLOOKUP(R[0]C[-2],R1C27:R250C35,9,0))"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F50"), Type:=xlFillDefault
Range("F3:F50").Select
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G50"), Type:=xlFillDefault
Range("G3:G50").Select
Range("F1:G2").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[2]C[1]0,""PLEASE CHECK THE FOLLOWING FORMULAS"",""ALL
FORMULAS ARE CURRENT"")"
Range("D1").Select
Range("B4").Select
Sheets("Conversions").Visible = False
Application.ScreenUpdating = True
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
tom tom is offline
external usenet poster
  
Posts: 570
Default Code hangs second time around
Joel,
Thanks for taking the time to look at the code.
In "Filter", I only set the range to 400 because that would ensure that it
would include all of my data. the actual data only goes down to ABOUT row
350, but changes somewhat from time to time...so I made it 400 to ensure that
any change would be included.

The same principle applies to the copy and paste from Z:Z to E3....the data
again is USUALLY only down to row 350, but varies as much as down to about
375 or so at times. That being said, there isn't an issue with running off of
the end of the sheet.

As I said, this code DOES work....as long as it's run only once...running
the second time, it hangs and Excel must be shut down completely before it
will work again. I'm thinking that there must be something that has to be
"reset" to run the code more than once without having to shut down Excel?

"Joel" wrote:

I had problems gettting the code to work the first time. Th e functtion
filter doesn't make sense if yu are working with one sheet. I think there
should be two sheets in function filter.

In filter you are going from row 400 to 1, then searching in the middle of
this range, then deleting in this range. Can't be done!

I also found a problem with one of you copy instructions. You are copying
an entire column Z and then trying to paste it starting at cell E3. The last
3 rows of the paste will go off the end off the worksheet. I change the
selection to the following

Range("Z1", Range("Z" & Rows.Count).End(xlUp)).Select





Sub Filter()
Dim MyRange As Range
Set MyRange = Sheets("DATA").Range("C17:C23")
For x = 400 To 1 Step -1
With Sheets("sheet1")
myvalue = .Cells(x, 36).Value
For Each c In MyRange
If myvalue = c.Value Then
.Rows(x).EntireRow.Delete
Exit For
End If
Next
End With
Next
Set MyRange = Sheets("DATA").Range("D17:D18")
For x = 400 To 1 Step -1
With Sheets("sheet1")
myvalue = .Cells(x, 31).Value
For Each c In MyRange
If myvalue = c.Value Then
.Rows(x).EntireRow.Delete
Exit For
End If
Next
End With
Next
Call GetOurs
End Sub

Sub GetOurs()
Application.ScreenUpdating = False
Sheets("Conversions").Visible = True
Sheets("Conversions").Select
Range("I3:I200").Select
Selection.Copy
Sheets("FRMLST").Select
Range("W3:W200").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Conversions").Select
Range("M3:M200").Select
Selection.Copy
Sheets("FRMLST").Select
Range("X3:X200").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("Z1").Select
Selection.AutoFill Destination:=Range("Z1:Z200"), Type:=xlFillDefault
Range("Z1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<"
Range("Z1", Range("Z" & Rows.Count).End(xlUp)).Select
Selection.Copy
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("Z1").Select
Selection.AutoFilter
Range("F3").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(R[0]C[-1],R1C27:R250C29,3,0)),"""",VLOOKUP(R[0]C[-1],R1C27:R250C29,3,0))"
Range("G3").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(R[0]C[-2],R1C27:R250C35,9,0)),"""",VLOOKUP(R[0]C[-2],R1C27:R250C35,9,0))"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F50"), Type:=xlFillDefault
Range("F3:F50").Select
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G50"), Type:=xlFillDefault
Range("G3:G50").Select
Range("F1:G2").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[2]C[1]0,""PLEASE CHECK THE FOLLOWING FORMULAS"",""ALL
FORMULAS ARE CURRENT"")"
Range("D1").Select
Range("B4").Select
Sheets("Conversions").Visible = False
Application.ScreenUpdating = True
End Sub


"Tom" wrote:

Hi gang,
The code below runs FINE when I first open Excel and run the macro. However,
if I run the macro a second time, it hangs and I have to press the ESC button
to get it to stop. If I completely close down Excel and re-open the file, the
macro will once again run properly if ran only once. Unfortunately, the only
message I get is that the "code has been interrupted" and it does not show me
which line has the error in it. I'm not even sure which of the two macros it
is hanging on. Any ideas???

Sub Filter()
Dim MyRange As Range
Set MyRange = Sheets("DATA").Range("C17:C23")
For x = 400 To 1 Step -1
myvalue = Cells(x, 36).Value
For Each c In MyRange
If myvalue = c.Value Then
Rows(x).EntireRow.Delete
Exit For
End If
Next
Next
Set MyRange = Sheets("DATA").Range("D17:D18")
For x = 400 To 1 Step -1
myvalue = Cells(x, 31).Value
For Each c In MyRange
If myvalue = c.Value Then
Rows(x).EntireRow.Delete
Exit For
End If
Next
Next
Application.Run "GetOurs"
End Sub
-----------------------------------------------------------------------------------------------
Sub GetOurs()
Application.ScreenUpdating = False
Sheets("Conversions").Visible = True
Sheets("Conversions").Select
Range("I3:I200").Select
Selection.Copy
Sheets("FRMLST").Select
Range("W3:W200").Select
Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Conversions").Select
Range("M3:M200").Select
Selection.Copy
Sheets("FRMLST").Select
Range("X3:X200").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("Z1").Select
Selection.AutoFill Destination:=Range("Z1:Z200"), Type:=xlFillDefault
Range("Z1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<"
Columns("Z:Z").Select
Selection.Copy
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("Z1").Select
Selection.AutoFilter
Range("F3").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(R[0]C[-1],R1C27:R250C29,3,0)),"""",VLOOKUP(R[0]C[-1],R1C27:R250C29,3,0))"
Range("G3").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISNA(VLOOKUP(R[0]C[-2],R1C27:R250C35,9,0)),"""",VLOOKUP(R[0]C[-2],R1C27:R250C35,9,0))"
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F50"), Type:=xlFillDefault
Range("F3:F50").Select
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G50"), Type:=xlFillDefault
Range("G3:G50").Select
Range("F1:G2").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=IF(R[2]C[1]0,""PLEASE CHECK THE FOLLOWING FORMULAS"",""ALL
FORMULAS ARE CURRENT"")"
Range("D1").Select
Range("B4").Select
Sheets("Conversions").Visible = False
Application.ScreenUpdating = True
End Sub

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import data "Hangs", A "time out" is needed. Help! :) John37309 Excel Programming 5 July 23rd 07 10:54 AM
excel exits unexpectedly or hangs the second time I open workbook r_m_i Excel Discussion (Misc queries) 0 February 9th 06 10:14 PM
VBA Hangs sponny Excel Programming 2 November 4th 05 02:58 PM
.NET code hangs setting up a chart Chris Magoun Excel Programming 1 July 25th 05 04:25 PM
Code hangs Excel ... Why? ... and how can i debug?? Simon May Excel Programming 1 January 17th 05 06:22 AM


All times are GMT +1. The time now is 11:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement