Code stops at range
in [Programming]
|
Prev: Excel 2002 - Iterations - Find the iteration sequence number
Next: Turning Spreadsheet into attachement in an Email
From: Pat on 16 Feb 2005 12:32 Why does the following code stop running when it reaches Range("A1:A1000").Select Application.DisplayAlerts = False Range("D8:J900").Select Selection.Copy Application.DisplayAlerts = False Workbooks.Open Filename:= _ "\\office\shareddocs\My Documents\Excel books 2005\PForm.xls" Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Range("A1:A1000").Select Selection.Replace What:="2", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Thank you if can help. Pat
From: Damian on 16 Feb 2005 12:55 AGH! This is exactly like the problem I'm having with VERY similar code. I'll post it again here since it might increase our collective chance of getting a response: This issue has me totally stumped. All the breakpoints are cleared. The IDE window does NOT pop-up. No lines are highlighted if the IDE window is already open. No error message appears or msgbox pops-up. The code just jumps to the Timekeepers.csv and stops(?) executing. Per Oli's suggestion I deleted the original module and pasted the code into a new one, but that did not help. I've tried adding Wait times into the code, I've tried three different ways of selecting the desired cell and inserting the formula, but it always stops at the point that the new document opens. The line (flagged below) is as follows: Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE Could it be because its a CSV file instead of an Excel file? I don't know... Please note it runs perfectly if I step through it one line at a time. Sub TimekeeperUpdate() ' TimekeeperUpdate Macro ' Macro recorded 2/15/2005 by Damian Carrillo ' Keyboard Shortcut: Ctrl+Shift+T Dim FileScan As Variant Dim FilePresence As String Set FileScan = Application.FileSearch With FileScan .LookIn = "F:\Importer\CMSDISB1\Disbursements\zAutomation Files" .Filename = "Timekeepers.csv" If .Execute > 0 Then 'Prevent file from being overwritten if already exists Let FilePresence = True Else Let FilePresence = False 'Duplicate Source Timekeeper file Dim SourceFile, DestinationFile SourceFile = "W:\ERS\SERVER\Manager\Validation\Timekeeprs.csv" 'Define source file name. DestinationFile = "F:\Importer\CMSDISB1\Disbursements\zAutomation Files\Timekeepers.csv" 'Define target file name. FileCopy SourceFile, DestinationFile 'Copy source to target. End If End With 'Clear Old Data from Timekeeper.xls Windows("Timekeepers.xls").Activate Range("D2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Application.CutCopyMode = False Selection.ClearContents 'Prepare new copy of Timekeeper.csv file for data transfer to Timekeeper.xls Workbooks.Open Filename:= _ "F:\Importer\CMSDISB1\Disbursements\zAutomation Files\Timekeepers.csv" Windows("Timekeepers.csv").Activate Application.Wait (Now + TimeValue("0:00:01")) On Error Resume Next Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]" 'Merge Data into a single column Selection.AutoFill Destination:=Range("C1", ActiveCell.SpecialCells(xlLastCell)), Type:=xlFillDefault 'Expand formula Range("C1", ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Windows("Timekeepers.xls").Activate 'Paste new data Range("D2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.TextToColumns Destination:=Range("D2"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _ 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _ TrailingMinusNumbers:=True Range("A1", ActiveCell.SpecialCells(xlLastCell)).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers 'Resort new dataset On Error GoTo 0 'Stop subroutine if unable to save file ActiveWorkbook.Save 'Save new dataset Windows("Timekeepers.csv").Activate ActiveWindow.Close SaveChanges:=True 'Close source document Kill "F:\Importer\CMSDISB1\Disbursements\zAutomation Files\Timekeepers.csv" End Sub The following is code I am using to re-populate a spreadsheet with updated data. I tested it extensively in the Editor and it runs fine, but when I try to run the macro, it stops at the same exact spot every time and I don't know why. Can someone please help me? The line (flagged below) is as follows: Range("C1").Select '<<<PROGRAM STOPS AT THIS LINE I must be missing something obvious about the statement that halts the macro, but why doesn't it stop it from working when I'm manually stepping through the program in the Visual Basic Editor? "Pat" wrote: > Why does the following code stop running when it reaches > Range("A1:A1000").Select > > > > > > Application.DisplayAlerts = False > > Range("D8:J900").Select > Selection.Copy > Application.DisplayAlerts = False > Workbooks.Open Filename:= _ > "\\office\shareddocs\My Documents\Excel books 2005\PForm.xls" > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= > _ > False, Transpose:=False > Application.CutCopyMode = False > Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _ > SearchOrder:=xlByRows, MatchCase:=False > Range("A1:A1000").Select > Selection.Replace What:="2", Replacement:="", LookAt:=xlPart, _ > SearchOrder:=xlByRows, MatchCase:=False > > > Thank you if can help. > Pat > > > >
From: Bob Phillips on 16 Feb 2005 13:08 Pat, I have just tested this and I don't get the crash. I don't understand why you open another workbook in the middle. Could you explain exactly what you are trying to do? -- HTH RP (remove nothere from the email address if mailing direct) "Pat" <glass_patrick(a)hotmail.com> wrote in message news:OTLwS2EFFHA.2676(a)TK2MSFTNGP12.phx.gbl... > Why does the following code stop running when it reaches > Range("A1:A1000").Select > > > > > > Application.DisplayAlerts = False > > Range("D8:J900").Select > Selection.Copy > Application.DisplayAlerts = False > Workbooks.Open Filename:= _ > "\\office\shareddocs\My Documents\Excel books 2005\PForm.xls" > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= > _ > False, Transpose:=False > Application.CutCopyMode = False > Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _ > SearchOrder:=xlByRows, MatchCase:=False > Range("A1:A1000").Select > Selection.Replace What:="2", Replacement:="", LookAt:=xlPart, _ > SearchOrder:=xlByRows, MatchCase:=False > > > Thank you if can help. > Pat > > >
From: Pat on 16 Feb 2005 14:02
> I don't understand why you open another workbook in the middle. Could you > explain exactly what you are trying to do? The PForm.xls workbook will be saved as .csv file Here is the remainder of the code: LastRow = ActiveSheet.UsedRange.Rows.Count Range(Cells(LastRow + 1, 1), Cells(LastRow + 200, 1)).Select Selection.EntireRow.Delete ActiveWorkbook.SaveAs Filename:= _ "C:\HLSexcel\RHLS.csv", _ FileFormat:=xlCSV, CreateBackup:=False '"\\office\shareddocs\My Documents\Excel books 2005\RHLS.csv", _ 'FileFormat:=xlCSV, CreateBackup:=False Application.DisplayAlerts = True Windows("Management.xls").Activate Range("G8").Select Pat "Bob Phillips" <bob.phillips(a)notheretiscali.co.uk> wrote in message news:Ov9S8JFFFHA.3284(a)TK2MSFTNGP09.phx.gbl... > Pat, > > I have just tested this and I don't get the crash. > > I don't understand why you open another workbook in the middle. Could you > explain exactly what you are trying to do? > > -- > > HTH > > RP > (remove nothere from the email address if mailing direct) > > > "Pat" <glass_patrick(a)hotmail.com> wrote in message > news:OTLwS2EFFHA.2676(a)TK2MSFTNGP12.phx.gbl... > > Why does the following code stop running when it reaches > > Range("A1:A1000").Select > > > > > > > > > > > > Application.DisplayAlerts = False > > > > Range("D8:J900").Select > > Selection.Copy > > Application.DisplayAlerts = False > > Workbooks.Open Filename:= _ > > "\\office\shareddocs\My Documents\Excel books 2005\PForm.xls" > > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, > SkipBlanks:= > > _ > > False, Transpose:=False > > Application.CutCopyMode = False > > Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _ > > SearchOrder:=xlByRows, MatchCase:=False > > Range("A1:A1000").Select > > Selection.Replace What:="2", Replacement:="", LookAt:=xlPart, _ > > SearchOrder:=xlByRows, MatchCase:=False > > > > > > Thank you if can help. > > Pat > > > > > > > > |