Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling on my program
I wrote a program that goes through a bunch of records and then spits out an
rdf file. My problem is, when it asks for the first upper most record and the person hits cancel on the dialog box, I get a run-time error '424'. I am not sure how to handle this, and I would like to have a dialog box pulled up asking are you sure? Below is the code below.... After the code below, I have a series of select statements that follow in my program... Sub GetRows() Dim FirstCell As Range, LastCell As Range Dim Firstrow As Long, Lastrow As Long Dim Wordstring As String Dim filePath As String Dim I As Integer Dim FName As Variant Wordstring = "$RDFILE 1" & vbCrLf & _ "$DATM " & Date & " " & Time & vbCrLf & _ "$RIREG 1" & vbCrLf & _ "$DTYPE BATCH: CHEMIST" & vbCrLf & _ "$DATUM REIDHAAJ" & vbCrLf & _ "$DTYPE BATCH: STRUCT_CMNT" & vbCrLf & _ "$DATUM [NUCLEIC ACID]" & vbCrLf & _ "$DTYPE STRUCTURE" & vbCrLf & _ "$DATUM $MFMT" filePath = ActiveWorkbook.Path & "\Seqfile.rdf" Open filePath For Output As #1 Print #1, Wordstring Do Set FirstCell = Application.InputBox("Enter top left cell - ONE cell only ", Type:=8) '''After this line, I would like to have an error handler for the Cancel button, but it fails miserably no matter what I do. I'm not sure how to handle this one. Loop Until FirstCell.Count = 1 Firstrow = FirstCell.Row Do Set LastCell = Application.InputBox("Enter bottom right cell - ONE cellonly ", Type:=8) '''I would like to have an error handler for this line too Loop Until LastCell.Count = 1 Lastrow = LastCell.Row Firstrow = Int(Firstrow) Lastrow = Int(Lastrow) MsgBox Firstrow & " " & Lastrow Range(Firstrow & ":" & Lastrow).Select For I = Firstrow To Lastrow If IsEmpty(Cells(I, "C").Value) Then Dim G As Variant Dim H As Variant Select Case IsEmpty(Cells(I, "G").Value) Or Cells(I, "G").Value = " " |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling on my program
Brett,
Here's the general idea: Dim myReturn As Range On Error Resume Next Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You pressed Cancel!" Exit Sub Else MsgBox myReturn.Address End If Of course, instead of exiting the sub, you could go back and ask for the cell again: Dim myReturn As Range On Error Resume Next GetCell: Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You can't press Cancel!" GoTo GetCell Else MsgBox myReturn.Address End If HTH, Bernie MS Excel MVP "Brett Smith" wrote in message ... I wrote a program that goes through a bunch of records and then spits out an rdf file. My problem is, when it asks for the first upper most record and the person hits cancel on the dialog box, I get a run-time error '424'. I am not sure how to handle this, and I would like to have a dialog box pulled up asking are you sure? Below is the code below.... After the code below, I have a series of select statements that follow in my program... Sub GetRows() Dim FirstCell As Range, LastCell As Range Dim Firstrow As Long, Lastrow As Long Dim Wordstring As String Dim filePath As String Dim I As Integer Dim FName As Variant Wordstring = "$RDFILE 1" & vbCrLf & _ "$DATM " & Date & " " & Time & vbCrLf & _ "$RIREG 1" & vbCrLf & _ "$DTYPE BATCH: CHEMIST" & vbCrLf & _ "$DATUM REIDHAAJ" & vbCrLf & _ "$DTYPE BATCH: STRUCT_CMNT" & vbCrLf & _ "$DATUM [NUCLEIC ACID]" & vbCrLf & _ "$DTYPE STRUCTURE" & vbCrLf & _ "$DATUM $MFMT" filePath = ActiveWorkbook.Path & "\Seqfile.rdf" Open filePath For Output As #1 Print #1, Wordstring Do Set FirstCell = Application.InputBox("Enter top left cell - ONE cell only ", Type:=8) '''After this line, I would like to have an error handler for the Cancel button, but it fails miserably no matter what I do. I'm not sure how to handle this one. Loop Until FirstCell.Count = 1 Firstrow = FirstCell.Row Do Set LastCell = Application.InputBox("Enter bottom right cell - ONE cellonly ", Type:=8) '''I would like to have an error handler for this line too Loop Until LastCell.Count = 1 Lastrow = LastCell.Row Firstrow = Int(Firstrow) Lastrow = Int(Lastrow) MsgBox Firstrow & " " & Lastrow Range(Firstrow & ":" & Lastrow).Select For I = Firstrow To Lastrow If IsEmpty(Cells(I, "C").Value) Then Dim G As Variant Dim H As Variant Select Case IsEmpty(Cells(I, "G").Value) Or Cells(I, "G").Value = " " |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling on my program
Thanks Bernie,
Is there a way that I can control this so that if the person presses the cancel button twice it exits the sub? Like if they hit cancel once, it asks are you sure, and if they hit it twice it exits completely? Thanks! Brett "Bernie Deitrick" wrote: Brett, Here's the general idea: Dim myReturn As Range On Error Resume Next Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You pressed Cancel!" Exit Sub Else MsgBox myReturn.Address End If Of course, instead of exiting the sub, you could go back and ask for the cell again: Dim myReturn As Range On Error Resume Next GetCell: Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You can't press Cancel!" GoTo GetCell Else MsgBox myReturn.Address End If HTH, Bernie MS Excel MVP "Brett Smith" wrote in message ... I wrote a program that goes through a bunch of records and then spits out an rdf file. My problem is, when it asks for the first upper most record and the person hits cancel on the dialog box, I get a run-time error '424'. I am not sure how to handle this, and I would like to have a dialog box pulled up asking are you sure? Below is the code below.... After the code below, I have a series of select statements that follow in my program... Sub GetRows() Dim FirstCell As Range, LastCell As Range Dim Firstrow As Long, Lastrow As Long Dim Wordstring As String Dim filePath As String Dim I As Integer Dim FName As Variant Wordstring = "$RDFILE 1" & vbCrLf & _ "$DATM " & Date & " " & Time & vbCrLf & _ "$RIREG 1" & vbCrLf & _ "$DTYPE BATCH: CHEMIST" & vbCrLf & _ "$DATUM REIDHAAJ" & vbCrLf & _ "$DTYPE BATCH: STRUCT_CMNT" & vbCrLf & _ "$DATUM [NUCLEIC ACID]" & vbCrLf & _ "$DTYPE STRUCTURE" & vbCrLf & _ "$DATUM $MFMT" filePath = ActiveWorkbook.Path & "\Seqfile.rdf" Open filePath For Output As #1 Print #1, Wordstring Do Set FirstCell = Application.InputBox("Enter top left cell - ONE cell only ", Type:=8) '''After this line, I would like to have an error handler for the Cancel button, but it fails miserably no matter what I do. I'm not sure how to handle this one. Loop Until FirstCell.Count = 1 Firstrow = FirstCell.Row Do Set LastCell = Application.InputBox("Enter bottom right cell - ONE cellonly ", Type:=8) '''I would like to have an error handler for this line too Loop Until LastCell.Count = 1 Lastrow = LastCell.Row Firstrow = Int(Firstrow) Lastrow = Int(Lastrow) MsgBox Firstrow & " " & Lastrow Range(Firstrow & ":" & Lastrow).Select For I = Firstrow To Lastrow If IsEmpty(Cells(I, "C").Value) Then Dim G As Variant Dim H As Variant Select Case IsEmpty(Cells(I, "G").Value) Or Cells(I, "G").Value = " " |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling on my program
Brett,
Dim myReturn As Range Dim Tried As Boolean Tried = False GetCell: On Error Resume Next Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You pressed Cancel" & IIf(Tried, " AGAIN! Good-bye!", "!") If Tried Then Exit Sub Tried = True GoTo GetCell Else MsgBox myReturn.Address End If HTH, Bernie MS Excel MVP "Brett Smith" wrote in message ... Thanks Bernie, Is there a way that I can control this so that if the person presses the cancel button twice it exits the sub? Like if they hit cancel once, it asks are you sure, and if they hit it twice it exits completely? Thanks! Brett "Bernie Deitrick" wrote: Brett, Here's the general idea: Dim myReturn As Range On Error Resume Next Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You pressed Cancel!" Exit Sub Else MsgBox myReturn.Address End If Of course, instead of exiting the sub, you could go back and ask for the cell again: Dim myReturn As Range On Error Resume Next GetCell: Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You can't press Cancel!" GoTo GetCell Else MsgBox myReturn.Address End If HTH, Bernie MS Excel MVP "Brett Smith" wrote in message ... I wrote a program that goes through a bunch of records and then spits out an rdf file. My problem is, when it asks for the first upper most record and the person hits cancel on the dialog box, I get a run-time error '424'. I am not sure how to handle this, and I would like to have a dialog box pulled up asking are you sure? Below is the code below.... After the code below, I have a series of select statements that follow in my program... Sub GetRows() Dim FirstCell As Range, LastCell As Range Dim Firstrow As Long, Lastrow As Long Dim Wordstring As String Dim filePath As String Dim I As Integer Dim FName As Variant Wordstring = "$RDFILE 1" & vbCrLf & _ "$DATM " & Date & " " & Time & vbCrLf & _ "$RIREG 1" & vbCrLf & _ "$DTYPE BATCH: CHEMIST" & vbCrLf & _ "$DATUM REIDHAAJ" & vbCrLf & _ "$DTYPE BATCH: STRUCT_CMNT" & vbCrLf & _ "$DATUM [NUCLEIC ACID]" & vbCrLf & _ "$DTYPE STRUCTURE" & vbCrLf & _ "$DATUM $MFMT" filePath = ActiveWorkbook.Path & "\Seqfile.rdf" Open filePath For Output As #1 Print #1, Wordstring Do Set FirstCell = Application.InputBox("Enter top left cell - ONE cell only ", Type:=8) '''After this line, I would like to have an error handler for the Cancel button, but it fails miserably no matter what I do. I'm not sure how to handle this one. Loop Until FirstCell.Count = 1 Firstrow = FirstCell.Row Do Set LastCell = Application.InputBox("Enter bottom right cell - ONE cellonly ", Type:=8) '''I would like to have an error handler for this line too Loop Until LastCell.Count = 1 Lastrow = LastCell.Row Firstrow = Int(Firstrow) Lastrow = Int(Lastrow) MsgBox Firstrow & " " & Lastrow Range(Firstrow & ":" & Lastrow).Select For I = Firstrow To Lastrow If IsEmpty(Cells(I, "C").Value) Then Dim G As Variant Dim H As Variant Select Case IsEmpty(Cells(I, "G").Value) Or Cells(I, "G").Value = " " |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling on my program
I tried it and I still get the same error which is Runtime Error '424', the
most unsolvable and annoying error I ever encountered. Is there a reason why? Thanks! Brett "Brett Smith" wrote: Thanks Bernie, Is there a way that I can control this so that if the person presses the cancel button twice it exits the sub? Like if they hit cancel once, it asks are you sure, and if they hit it twice it exits completely? Thanks! Brett "Bernie Deitrick" wrote: Brett, Here's the general idea: Dim myReturn As Range On Error Resume Next Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You pressed Cancel!" Exit Sub Else MsgBox myReturn.Address End If Of course, instead of exiting the sub, you could go back and ask for the cell again: Dim myReturn As Range On Error Resume Next GetCell: Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You can't press Cancel!" GoTo GetCell Else MsgBox myReturn.Address End If HTH, Bernie MS Excel MVP "Brett Smith" wrote in message ... I wrote a program that goes through a bunch of records and then spits out an rdf file. My problem is, when it asks for the first upper most record and the person hits cancel on the dialog box, I get a run-time error '424'. I am not sure how to handle this, and I would like to have a dialog box pulled up asking are you sure? Below is the code below.... After the code below, I have a series of select statements that follow in my program... Sub GetRows() Dim FirstCell As Range, LastCell As Range Dim Firstrow As Long, Lastrow As Long Dim Wordstring As String Dim filePath As String Dim I As Integer Dim FName As Variant Wordstring = "$RDFILE 1" & vbCrLf & _ "$DATM " & Date & " " & Time & vbCrLf & _ "$RIREG 1" & vbCrLf & _ "$DTYPE BATCH: CHEMIST" & vbCrLf & _ "$DATUM REIDHAAJ" & vbCrLf & _ "$DTYPE BATCH: STRUCT_CMNT" & vbCrLf & _ "$DATUM [NUCLEIC ACID]" & vbCrLf & _ "$DTYPE STRUCTURE" & vbCrLf & _ "$DATUM $MFMT" filePath = ActiveWorkbook.Path & "\Seqfile.rdf" Open filePath For Output As #1 Print #1, Wordstring Do Set FirstCell = Application.InputBox("Enter top left cell - ONE cell only ", Type:=8) '''After this line, I would like to have an error handler for the Cancel button, but it fails miserably no matter what I do. I'm not sure how to handle this one. Loop Until FirstCell.Count = 1 Firstrow = FirstCell.Row Do Set LastCell = Application.InputBox("Enter bottom right cell - ONE cellonly ", Type:=8) '''I would like to have an error handler for this line too Loop Until LastCell.Count = 1 Lastrow = LastCell.Row Firstrow = Int(Firstrow) Lastrow = Int(Lastrow) MsgBox Firstrow & " " & Lastrow Range(Firstrow & ":" & Lastrow).Select For I = Firstrow To Lastrow If IsEmpty(Cells(I, "C").Value) Then Dim G As Variant Dim H As Variant Select Case IsEmpty(Cells(I, "G").Value) Or Cells(I, "G").Value = " " |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Handling on my program
Thanks Bernie,
the reason why it didn't work the first time is because I did not declare the On Error Resume Next expression up top and that is why I kept getting Runtime Error '424'...EHRHH, unlucky number. But now everything works perfectly. Thanks a billion!! Brett "Bernie Deitrick" wrote: Brett, Dim myReturn As Range Dim Tried As Boolean Tried = False GetCell: On Error Resume Next Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You pressed Cancel" & IIf(Tried, " AGAIN! Good-bye!", "!") If Tried Then Exit Sub Tried = True GoTo GetCell Else MsgBox myReturn.Address End If HTH, Bernie MS Excel MVP "Brett Smith" wrote in message ... Thanks Bernie, Is there a way that I can control this so that if the person presses the cancel button twice it exits the sub? Like if they hit cancel once, it asks are you sure, and if they hit it twice it exits completely? Thanks! Brett "Bernie Deitrick" wrote: Brett, Here's the general idea: Dim myReturn As Range On Error Resume Next Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You pressed Cancel!" Exit Sub Else MsgBox myReturn.Address End If Of course, instead of exiting the sub, you could go back and ask for the cell again: Dim myReturn As Range On Error Resume Next GetCell: Set myReturn = Application.InputBox( _ "Enter bottom right cell - ONE cell only ", Type:=8) On Error GoTo 0 If myReturn Is Nothing Then MsgBox "You can't press Cancel!" GoTo GetCell Else MsgBox myReturn.Address End If HTH, Bernie MS Excel MVP "Brett Smith" wrote in message ... I wrote a program that goes through a bunch of records and then spits out an rdf file. My problem is, when it asks for the first upper most record and the person hits cancel on the dialog box, I get a run-time error '424'. I am not sure how to handle this, and I would like to have a dialog box pulled up asking are you sure? Below is the code below.... After the code below, I have a series of select statements that follow in my program... Sub GetRows() Dim FirstCell As Range, LastCell As Range Dim Firstrow As Long, Lastrow As Long Dim Wordstring As String Dim filePath As String Dim I As Integer Dim FName As Variant Wordstring = "$RDFILE 1" & vbCrLf & _ "$DATM " & Date & " " & Time & vbCrLf & _ "$RIREG 1" & vbCrLf & _ "$DTYPE BATCH: CHEMIST" & vbCrLf & _ "$DATUM REIDHAAJ" & vbCrLf & _ "$DTYPE BATCH: STRUCT_CMNT" & vbCrLf & _ "$DATUM [NUCLEIC ACID]" & vbCrLf & _ "$DTYPE STRUCTURE" & vbCrLf & _ "$DATUM $MFMT" filePath = ActiveWorkbook.Path & "\Seqfile.rdf" Open filePath For Output As #1 Print #1, Wordstring Do Set FirstCell = Application.InputBox("Enter top left cell - ONE cell only ", Type:=8) '''After this line, I would like to have an error handler for the Cancel button, but it fails miserably no matter what I do. I'm not sure how to handle this one. Loop Until FirstCell.Count = 1 Firstrow = FirstCell.Row Do Set LastCell = Application.InputBox("Enter bottom right cell - ONE cellonly ", Type:=8) '''I would like to have an error handler for this line too Loop Until LastCell.Count = 1 Lastrow = LastCell.Row Firstrow = Int(Firstrow) Lastrow = Int(Lastrow) MsgBox Firstrow & " " & Lastrow Range(Firstrow & ":" & Lastrow).Select For I = Firstrow To Lastrow If IsEmpty(Cells(I, "C").Value) Then Dim G As Variant Dim H As Variant Select Case IsEmpty(Cells(I, "G").Value) Or Cells(I, "G").Value = " " |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling | Excel Programming | |||
Error Handling | Excel Programming | |||
Error Handling | Excel Programming | |||
Error handling with a handling routine | Excel Programming | |||
error handling off?? | Excel Programming |